﻿using System;
using System.Data;
using System.Data.OracleClient;
using Antu.Core.Provider;
using Antu.DataAccess;
using Antu.Utilities;
using System.Drawing;


namespace Antu.WorkFlowFactory
{
    /// <summary>
    /// 基于目前st表中字段都很不统一的局面，这里不采用对象方式。注意：该工程只涉及ST表操作，不涉及XT等扩展表
    /// </summary>
    public abstract class FlowFactoryProvider : AbstractProvider
    {
        #region 实例化数据库连接(实现类必须实现)
        public IDataAccess dataAccess()
        {
            return dataAccess(false);
        }
        public abstract IDataAccess dataAccess(bool transaction);
        #endregion

        public DataTable GetAllUser(string query)
        {
            string sql = "select * from st_user";
            DataTable dt;
            dataAccess().RunSql(sql, out dt);
            return dt;
        }
        public DataTable GetUser(string userid)
        {
            string sql = "select * from st_user where userid ='" + userid + "' ";
            DataTable dt;
            dataAccess().RunSql(sql, out dt);
            return dt;
        }
        public string GetUser(string username, string pwd)
        {
            string sql = "select userid from st_user where login_name ='" + username + "'  and password='" + pwd + "' ";
            return dataAccess().GetValue(sql);
        }

        /// <summary>
        /// 获取用户有权限的流程
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public DataTable GetWorkFlowListByUserId(string userid, StepType steptype)
        {
            string sql = "select m.*  from st_workflow m, st_step n where 1 = 1 and m.WID in (select WID from st_group_in_step where GID in (select GID from st_user_group t where t.userid = '" + userid + "') And step_type = '" + (int)steptype + "') and m.WID = n.Wid and n.step_type = '" + (int)steptype + "' and m.rot = 0 ";
            DataTable dt;
            dataAccess().RunSql(sql, out dt);
            return dt;
        }

        /// <summary>
        /// 获取用户所能受理的流程信息
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public DataTable GetStartWorkFlowListByUserId(string userid)
        {
            return GetWorkFlowListByUserId(userid, StepType.Start);
        }
        /// <summary>
        /// 获取流程名称
        /// </summary>
        /// <param name="wid"></param>
        /// <returns></returns>
        public string GetWorkFlowName(string wid)
        {
            string sql = "select wname from st_workflow where wid='" + wid + "'";
            return dataAccess().GetValue(sql);
        }
        public string GetStepName(string ctlid, string wid)
        {
            string sql = "select sname from st_step where ctlid	='" + ctlid + "' and wid='" + wid + "'";
            return dataAccess().GetValue(sql);
        }
        /// <summary>
        /// 根据iid获取他所对应的wid
        /// </summary>
        /// <param name="iid"></param>
        /// <returns></returns>
        public string GetInstanceWid(string iid)
        {
            string sql = "select wid from st_instance where iid='" + iid + "'";
            return dataAccess().GetValue(sql);
        }
        /// <summary>
        /// 获取该用户有权限处理的案件(包括被委托案件)
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public DataTable GetInstanceListByUserId(string userid, string query)
        {
            string adminid = System.Configuration.ConfigurationManager.AppSettings["OfficeSearch"].ToString();
            string[] aids = adminid.Split(',');
            string sql = "";
            bool flag = false;
            foreach (string item in aids)
            {
                if (userid == item)
                {
                    flag = true;
                }
            }
            if (flag)
            {
                sql = "select * from sv_getinstancelist a where 1=1 " + query + " order by  接收时间 desc ";
            }
            else
            {
                sql = @"select * from sv_getinstancelist a where (userid = '" + userid +
   @"' or userid in (select userid from st_delegate Where delegate_userid = '" + userid + @"' 
And (delegate_task = a.案件类型 or delegate_task = '全部') and begin_time <= Sysdate 
And end_time >= Sysdate)) " + query + " order by  接收时间 desc ";
            }
            DataTable dt;
            dataAccess().RunSql(sql, out dt);
            return dt;
        }
        public DataTable GetInstanceRollBack(string iid)
        {
            string sql = @"select * from st_instance a where iid='" + iid + "' ";
            DataTable dt;
            dataAccess().RunSql(sql, out dt);
            return dt;
        }
        /// <summary>
        /// 根据iid获取案件的(当多流向时只能传到多流向开始节点)
        /// </summary>
        /// <param name="iid"></param>
        /// <returns></returns>
        public DataTable GetBackStepByiid(string iid, string userid, string rstep)
        {
            DataTable tempDT = null;
            IDataAccess ida = dataAccess();
            string sql = @"select t.*  from st_work_item t  where iid = " + iid + " and step='" + rstep + "'   and userid='" + userid + "' ";
            ida.RunSql(sql, out tempDT);
            if (tempDT != null && tempDT.Rows.Count > 0)
            {
                StepType stepTypeByStep = GetStepTypeByStep(Convert.ToInt64(iid), tempDT.Rows[0]["sendstepctlid"].ToString());
                if (stepTypeByStep == StepType.MutiFlowStart)
                {
                    sql = @"select * from (select wiid,
                                userid,(select user_name from st_user
                                where st_user.userid = st_work_item_hist.userid) 经办人,
                                STEP 岗位,ACCEPTED_TIME 接受时间,
                                SUBMIT_TIME 提交时间, to_char(EXUSEDTIME) 办理时间
                                from st_work_item_hist where iid = " + iid + @"
                                order by accepted_time desc) tw where 岗位='" + tempDT.Rows[0]["sendstep"].ToString() + "' and  rownum<=1";
                    ida.RunSql(sql, out tempDT);
                    if (tempDT != null && tempDT.Rows.Count > 0)
                    {
                        for (int i = 0; i < tempDT.Rows.Count; i++)
                        {
                            tempDT.Rows[i]["经办人"] = tempDT.Rows[i]["经办人"].ToString() + "(" + tempDT.Rows[i]["岗位"].ToString() + ")";
                        }
                    }
                    return tempDT;
                }
            }


            DataTable dt = null;
            dt = GetFlowInfo(iid);
            string userName = dt.Rows[dt.Rows.Count - 1]["userid"].ToString();
            string step = dt.Rows[dt.Rows.Count - 1]["岗位"].ToString();

            tempDT = dt.Clone();
            //tempDT.Rows.Add(dt.Rows[0].ItemArray);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                int check = 1;
                for (int j = 0; j < tempDT.Rows.Count; j++)
                {
                    if ((dt.Rows[i]["userid"].ToString() == tempDT.Rows[j]["userid"].ToString()) && (dt.Rows[i]["岗位"].ToString() == tempDT.Rows[j]["岗位"].ToString()))
                    {
                        check = 0;
                        break;
                    }
                }
                if (check == 1)
                {
                    if ((dt.Rows[i]["userid"].ToString() != userName) || (dt.Rows[i]["岗位"].ToString() != step))
                    {
                        tempDT.Rows.Add(dt.Rows[i].ItemArray);
                    }
                }
            }

            for (int i = 0; i < tempDT.Rows.Count; i++)
            {
                tempDT.Rows[i]["经办人"] = tempDT.Rows[i]["经办人"].ToString() + "(" + tempDT.Rows[i]["岗位"].ToString() + ")";

            }
            return tempDT;
        }
        public DataTable GetFlowInfo(string wid)
        {
            DataTable flowInfo = null;
            DataTable tempDt = null;
            string sql = string.Format("select count(*) from st_workflow where wid=(select wid from st_instance where iid='{0}')  and wname in('解封流程', '处置流程', '扣划流程', '预查封流程', '继续查封流程')", wid);
            string hist_sql = string.Format("select wiid,userid,(select user_name from st_user where st_user.userid = st_work_item_hist.userid) 经办人,STEP 岗位,ACCEPTED_TIME 接受时间,SUBMIT_TIME 提交时间,to_char(EXUSEDTIME) 办理时间 from st_work_item_hist where iid = '{0}' order by wiid", wid);
            string work_item_sql = string.Format("select wiid,userid,(select user_name from st_user where st_user.userid = st_work_item.userid) 经办人,STEP 岗位,ACCEPTED_TIME 接受时间,ACCEPTED_TIME 提交时间,'办理中' 办理时间 from st_work_item where iid = '{0}' and active != -1 order by wiid", wid);
            IDataAccess ida = dataAccess();
            if (ida.GetValue(sql) != "0") hist_sql = string.Format("select wiid,userid,(select user_name from st_user where st_user.userid = st_work_item_hist.userid) 经办人,STEP 岗位,ACCEPTED_TIME 接受时间,SUBMIT_TIME 提交时间,to_char(EXUSEDTIME) 办理时间 from st_work_item_hist where iid = '{0}' and step<>'开始' order by wiid", wid);
            try
            {
                ida.RunSql(hist_sql, out flowInfo);
                ida.RunSql(work_item_sql, out tempDt);
            }
            catch (Exception eGetHist)
            {
                throw new Exception("重新取得历史表和当前表的办理数据出错！" + eGetHist.Message);
            }
            for (int i = 0; i < tempDt.Rows.Count; i++)
            {
                tempDt.Rows[i]["提交时间"] = System.DBNull.Value;
                flowInfo.Rows.Add(tempDt.Rows[i].ItemArray);
            }
            for (int i = 0; i < flowInfo.Rows.Count; i++)
            {

                //IDataParameter[] idParams = Antu.DataAccess.DataFactory.GetParameter(Antu.DataAccess.DatabaseType.Oracle, 1);
                //idParams[0].ParameterName = "vId";
                //idParams[0].Value = flowInfo.Rows[i]["wiid"].ToString();
                //ida.RunProc("antu_njgt_business.computeold_task_njgt", ref idParams);

                if (flowInfo.Rows[i]["办理时间"].ToString() != "办理中")
                {
                    try
                    {
                        flowInfo.Rows[i]["办理时间"] = GetWorkTime(flowInfo.Rows[i]["办理时间"].ToString());
                    }
                    catch (Exception eBLSJ)
                    {
                        throw new Exception("统计办理时间出错！" + eBLSJ.Message);
                    }
                }

            }

            ida.Close();
            return flowInfo;
        }
        public string GetWorkTime(string strSeconds)
        {

            long _seconds = Convert.ToInt64(strSeconds);
            string days = "";
            string hours = "";
            string minutes = "";
            string seconds = "";
            if ((_seconds / 25200) > 0)
            {
                days = Convert.ToString(_seconds / 25200) + "天";
            }
            if ((_seconds % 25200) / 3600 > 0)
            {
                hours = Convert.ToString((_seconds % 25200) / 3600) + "小时";
            }
            if (((_seconds % 25200) % 3600) / 60 > 0)
            {
                minutes = Convert.ToString(((_seconds % 25200) % 3600) / 60) + "分";
            }
            seconds = Convert.ToString(((_seconds % 25200) % 3600) % 60) + "秒";
            return days + hours + minutes + seconds;
        }
        public virtual DataTable GetRolesInStep(string wid, string flow, string iid, string userid)
        {
            if (flow == "岗位内提交") //如果是特殊的流向（岗位内提交），则例外处理
            {
                DataTable dt = new DataTable();
                dt.Columns.Add("group_name");
                DataRow dr = dt.NewRow();
                dr["group_name"] = "岗位内提交";
                dt.Rows.Add(dr);
                dt.AcceptChanges();
                return dt;
            }
            string sql = string.Format(@"select group_name from st_group u 
                   where u.groupid in (select gid from st_group_in_step where wid='{0}' and 
                    stpname=(select ename from st_flow where wid='{0}' and fname='{1}'))  order by sort", wid, flow);
            DataTable dtResult = new DataTable();
            dataAccess().RunSql(sql, out dtResult);
            return dtResult;
        }
        public virtual DataTable GetRoleUserInStep(long iid, long wiid, string groupname)
        {
            string sql = string.Format(@"Select userid,user_name From st_user k 
                Where userid In(Select userid From st_user_group Where gid In
                (select groupid from st_group where group_name='{0}')) order by k.usernumber", groupname);

            if (groupname == "岗位内提交")
            {
                sql = string.Format("select * from (select distinct(C.USERID), C.USER_NAME, c.usernumber from st_group_in_step a, st_user_group b, st_user c Where a.gid = b.gid And b.userid = c.userid And wid = (select wid from st_instance where iid = '{0}' and rownum <= 1) And stpname in (select stepctlid from st_work_item Where wiid = '{1}') And c.userid <> (SELECT UserId FROM ST_WORK_ITEM WHERE wiid = '{1}' and rownum <= 1)) order by usernumber", iid, wiid);
            }
            DataTable dtResult = new DataTable();
            dataAccess().RunSql(sql, out dtResult);
            return dtResult;
        }

        /// <summary>
        /// 获取用户有权限的所有流程
        /// </summary>
        /// <returns></returns>
        public virtual DataTable GetUserRightWname(string userid)
        {
            string query = "";
            if (!string.IsNullOrEmpty(userid))
            {
                query = " and ws.userid='" + userid + "' ";
            }
            string sql = " SELECT * FROM (select distinct wname from st_workflow w,st_instance i,st_work_item_stack ws where w.wid=i.wid and i.iid=ws.iid " + query + " ) order by wname";
            DataTable dt = new DataTable();
            dataAccess().RunSql(sql, out dt);
            return dt;
        }
        /// <summary>
        /// 获取有权限的查询列表数据信息
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public virtual DataTable GetUserRightWorkFlow(string query)
        {
            string sql = @"select i.*,w.wname,(Select caseid From zs_case_register Where iid=i.iid) caseid  from st_instance i, st_workflow w  where w.wid = i.wid " + query + " order by i.iid desc";
            DataTable dt = new DataTable();
            dataAccess().RunSql(sql, out dt);
            return dt;
        }

        public virtual DataTable GetUserRightWorkFlowStepByStep(string iid)
        {
            string sql = @"select iid,wiid,(select login_name from st_user where userid=t.userid) username,step,accepted_time,submit_time from (
select iid,wiid,userid,step,accepted_time,submit_time from st_work_item_hist where iid='" + iid + @"'
union all
select iid,wiid,userid,step,accepted_time,null as submit_time from st_work_item where iid='" + iid + "' and active!='-1') t order by accepted_time asc";
            DataTable dt = new DataTable();
            dataAccess().RunSql(sql, out dt);
            return dt;
        }
        /// <summary>
        /// 判断当前流程是否可以打开
        /// </summary>
        /// <param name="iid"></param>
        /// <param name="wiid"></param>
        /// <param name="ctlid"></param>
        /// <returns></returns>
        public bool CanOpen(string userid, string iid, string wiid, string step)
        {
            bool canOpened = true;
            string sql = "SELECT wiid FROM ST_Work_Item WHERE step='" + step + "' AND  iid='" + iid + "' AND wiid='" + wiid + "' and (Active=1 OR Active=0 )  and userid='" + userid + "' ";
            string hasRight = dataAccess().GetValue(sql);
            if (String.IsNullOrEmpty(hasRight))//已经同岗位的其它人处理过了
            {
                canOpened = false;
            }
            return canOpened;
        }
        /// <summary>
        /// 判断当前流程是否被人打开过了
        /// </summary>
        /// <param name="iid"></param>
        /// <param name="wiid"></param>
        /// <param name="ctlid"></param>
        /// <returns></returns>
        public bool IsOpen(string iid, string wiid, string step)
        {
            bool isOpened = false;
            string sql = string.Format("SELECT wiid FROM ST_Work_Item WHERE Active=1  and step='" + step + "' AND  iid='{0}' AND wiid<>'{1}'", iid, wiid);
            string hasOpen = dataAccess().GetValue(sql);
            if (!String.IsNullOrEmpty(hasOpen))//已经同岗位的其它人处理过了
            {
                isOpened = true;
            }
            return isOpened;
        }
        /// <summary>
        ///  设置业务设置为已经打开
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="iid"></param>
        /// <param name="wiid"></param>
        /// <param name="step"></param>
        public void SetOpened(string userid, string iid, string wiid, string step)
        {
            //业务件一打开后避免其它人修改的处理。另外将UserId置为自己，一般情况下UserId就是自己，特殊情况为权力委托时，在打开前为
            //委托人，所以要在打开后置为被委托人。
            //string sql = string.Format("UPDATE ST_WORK_ITEM SET active=1,userid='{0}' WHERE iid='{1}' AND wiid='{2}'",userid,iid,wiid,ida);
            //ida.RunSql(sql);


            IDataAccess ida = dataAccess(true);
            try
            {
                //这里做的处理是对同岗位上的其他人做限制，但不对多流向时的同级别的其他流向岗位的人员不做处理的
                string sql = string.Format("UPDATE ST_WORK_ITEM SET active=1 WHERE iid='{0}' and step='" + step + "'  AND wiid='{1}'", iid, wiid);

                //其它流转置为-1
                string ssql = string.Format("UPDATE ST_WORK_ITEM SET active=-1 WHERE iid='{0}'  and step='" + step + "'  AND wiid<>'{1}'", iid, wiid);
                ida.RunSql(sql);
                ida.RunSql(ssql);
                ida.Close(true);
            }
            catch (Exception)
            {
                ida.Close(false);
                throw;
            }
        }

        /// <summary>
        /// 回退案件
        /// </summary>
        /// <param name="wiid"></param>
        /// <param name="remark"></param>
        /// <param name="iid"></param>
        /// <param name="oldUserId"></param>
        /// <param name="oldStep"></param>
        /// <param name="newUserId"></param>
        /// <param name="NewStep"></param>
        /// <returns></returns>
        public bool Untread(long wiid, string remark, string iid, string oldUserId, string oldStep, string newUserId, string NewStep)
        {
            string sql = string.Empty;
            try
            {
                //得到现节点数据备用
                sql = "SELECT * FROM ST_WORK_ITEM WHERE  wiid='" + wiid.ToString() + "'";
                DataTable nowWorkItem;
                dataAccess().RunSql(sql, out nowWorkItem);
                sql = string.Format("select * from st_work_item_hist where wiid in (select max(wiid) from st_work_item_hist where iid='{0}' and userid = '{1}' and step = '{2}')", iid, newUserId, NewStep);
                DataTable oldWorkItem;
                dataAccess().RunSql(sql, out oldWorkItem);
                if (nowWorkItem.Rows.Count == 0 || oldWorkItem.Rows.Count == 0)
                    throw new Exception("发生异常，当前节点的数据不存在，可能是被删除了。");
                DataRow dr = nowWorkItem.Rows[0];
                string wid = GetWidByIID(dr["iid"].ToString());
                string stepCtlId = oldWorkItem.Rows[0]["stepctlid"].ToString();
                string sendStepCtlId = dr["stepctlid"].ToString();
                string stepGroupCtlId = oldWorkItem.Rows[0]["stepgroupctlid"].ToString();
                string stepGroup = oldWorkItem.Rows[0]["stepgroup"].ToString();

                Antu.DataAccess.IDataAccess ida = dataAccess(true);
                try
                {
                    string newwiid = "";
                    //然后先根据新的节点在WorkItem表新建记录
                    if (!New(wid, iid, wiid.ToString(), newUserId, NewStep, stepCtlId, sendStepCtlId, oldStep, oldUserId, remark, true, ref newwiid, ref ida, false, stepGroupCtlId, stepGroup))
                    {
                        ida.Close(false);
                        return false;
                    }
                    //将原数据推到历史表中
                    if (!ToHist(wiid.ToString(), ref ida))
                    {
                        ida.Close(false);
                        return false;
                    }
                    ida.Close(true);
                    return true;
                }
                catch (Exception err)
                {
                    ida.Close(false);
                    throw new Exception(err.Message);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        /// <summary>
        /// 新建一条流转记录
        /// </summary>
        /// <param name="wid">流程定义Id</param>
        /// <param name="iid">流程实例Id</param>
        /// <param name="prev_wiid">前一流转节点Id</param>
        /// <param name="userId">用户Id</param>
        /// <param name="step">节点名称</param>
        /// <param name="sendStep">前一节点</param>
        /// <param name="sendUserId">前一用户</param>
        /// <param name="remark">备注</param>
        /// <param name="isBack">是否为回退记录</param>
        /// <param name="ida">数据操作对象</param>
        /// <param name="IsRound">标示是否为传阅</param>
        /// <param name="stepGroup">岗位组时的名称，这时岗位名称实际上是角色名称了</param>
        /// <returns></returns>
        private bool New(string wid, string iid, string prev_wiid, string userId, string step, string stepCtlId, string sendStepCtlId, string sendStep, string sendUserId, string remark, bool isBack, ref string wiid, ref IDataAccess ida, bool IsRound, string stepGroupCtlId, string stepGroup)
        {
            //创建流程流向记录。先得到新的一个流程项最大号          
            Antu.DataAccess.IDataAccess da = dataAccess();//不纳入事务中
            wiid = GetWorkItemMax(long.Parse(iid), ref da).ToString();
            //从节点定义表中获取定义信息
            string sql = "SELECT * FROM ST_STEP WHERE Wid=:wid AND CtlId=:sname";
            IDataParameter[] Parameters = DataFactory.GetParameter(DatabaseType.Oracle, 2);
            Parameters[0].ParameterName = "wid";
            Parameters[0].DbType = DbType.String;
            Parameters[0].Value = wid;

            Parameters[1].ParameterName = "sname";
            Parameters[1].DbType = DbType.String;
            Parameters[1].Value = stepCtlId;
            if (!String.IsNullOrEmpty(stepGroupCtlId))
                Parameters[1].Value = stepGroupCtlId;
            DataTable dt = da.RunSql(sql, ref Parameters);
            string timeOut;
            string alarmTime;
            if (dt.Rows.Count > 0)
            {
                timeOut = dt.Rows[0]["TimeOut"].ToString();//办结要求时长
                alarmTime = dt.Rows[0]["ExAlarmTime"].ToString();//警告时长  
                if (String.IsNullOrEmpty(stepGroupCtlId))//不是岗位组
                {
                    step = dt.Rows[0]["SName"].ToString();
                }
            }
            else //没有在节点定义表中找到数据，如果是岗位
            {
                throw new Exception("没有在节点定义表中找到数据,无法完成操作！");
            }
            string beginTime = "null";
            if (!String.IsNullOrEmpty(timeOut) && timeOut.Trim() != "0")//设置了办结时间，则将开始督办时间设为当前系统时间
            {
                beginTime = "sysdate";
                sql = "SELECT exbeginmonitor FROM ST_Instance WHERE iid=" + iid;
                DataTable monitor;
                ida.RunSql(sql, out monitor);
                if (monitor.Rows.Count > 0 && String.IsNullOrEmpty(monitor.Rows[0][0].ToString()))//如果流程也还没开始督办，则开始督办
                {
                    sql = "UPDATE ST_Instance SET exbeginmonitor=sysdate WHERE iid=" + iid;
                    ida.RunSql(sql);
                }
            }

            string strIsBack = "0";
            if (isBack == true) strIsBack = "1";

            string isRound = "0";
            if (IsRound)   //传阅
                isRound = "2";
            string strSql = "INSERT INTO st_work_item(iid,wiid, prev_wiid, userid, step, locked, accepted_time,isback,"
                + "extotaltime,exalarmtime,exreceivertype,exbeginmonitor,sendstep,senduserid,remark,stepgroup,STEPCTLID,SENDSTEPCTLID,STEPGROUPCTLID)"
                + " VALUES(:iid,:wiid, :prev_wiid, :userid, :step, :locked,sysdate,:isback,:extotaltime,:exalarmtime,1,"
                + "" + beginTime + ",:sendstep,:senduserid,:remark,:stepgroup,:stepctlid,:sendstepctlid,:stepgroupctlid)";
            Parameters = DataFactory.GetParameter(DatabaseType.Oracle, 16);
            #region 赋参数值
            Parameters[0].ParameterName = "iid";
            Parameters[0].DbType = DbType.Decimal;
            Parameters[0].Value = iid;

            Parameters[1].ParameterName = "wiid";
            Parameters[1].DbType = DbType.Decimal;
            Parameters[1].Value = wiid;

            Parameters[2].ParameterName = "prev_wiid";
            Parameters[2].DbType = DbType.Decimal;
            Parameters[2].Value = prev_wiid;

            Parameters[3].ParameterName = "userid";
            Parameters[3].DbType = DbType.String;
            Parameters[3].Value = userId;

            Parameters[4].ParameterName = "step";
            Parameters[4].DbType = DbType.String;
            Parameters[4].Value = step;

            Parameters[5].ParameterName = "locked";
            Parameters[5].DbType = DbType.String;
            Parameters[5].Value = isRound;

            Parameters[6].ParameterName = "isback";
            Parameters[6].DbType = DbType.Int32;
            Parameters[6].Value = strIsBack;

            Parameters[7].ParameterName = "extotaltime";
            Parameters[7].DbType = DbType.Decimal;
            Parameters[7].Value = timeOut;

            Parameters[8].ParameterName = "exalarmtime";
            Parameters[8].DbType = DbType.Decimal;
            Parameters[8].Value = alarmTime;

            Parameters[9].ParameterName = "sendstep";
            Parameters[9].DbType = DbType.String;
            Parameters[9].Value = sendStep;

            Parameters[10].ParameterName = "senduserid";
            Parameters[10].DbType = DbType.String;
            Parameters[10].Value = sendUserId;

            Parameters[11].ParameterName = "remark";
            Parameters[11].DbType = DbType.String;
            Parameters[11].Value = remark;

            Parameters[12].ParameterName = "stepgroup";
            Parameters[12].DbType = DbType.String;
            Parameters[12].Value = stepGroup;

            Parameters[13].ParameterName = "stepctlid";
            Parameters[13].DbType = DbType.String;
            Parameters[13].Value = stepCtlId;

            Parameters[14].ParameterName = "sendstepctlid";
            Parameters[14].DbType = DbType.String;
            Parameters[14].Value = sendStepCtlId;

            Parameters[15].ParameterName = "stepgroupctlid";
            Parameters[15].DbType = DbType.String;
            Parameters[15].Value = stepGroupCtlId;

            #endregion
            ida.RunSql(strSql, ref Parameters);
            if (isBack == false)//不是回退时
            {
                //记录到回退使用列表中（用于回退时获取最近的回退路径）
                sql = "INSERT INTO ST_WORK_ITEM_STACK(IID,WIID,PREV_WIID,USERID,STEP) VALUES(:iid,:wiid,:prev_wiid,:userid,:step)";
                Parameters = DataFactory.GetParameter(DatabaseType.Oracle, 5);
                Parameters[0].ParameterName = "iid";
                Parameters[0].DbType = DbType.Decimal;
                Parameters[0].Value = iid;

                Parameters[1].ParameterName = "wiid";
                Parameters[1].DbType = DbType.Decimal;
                Parameters[1].Value = wiid;

                Parameters[2].ParameterName = "prev_wiid";
                Parameters[2].DbType = DbType.Decimal;
                Parameters[2].Value = prev_wiid;

                Parameters[3].ParameterName = "userid";
                Parameters[3].DbType = DbType.String;
                Parameters[3].Value = userId;

                Parameters[4].ParameterName = "step";
                Parameters[4].DbType = DbType.String;
                Parameters[4].Value = stepCtlId;
                ida.RunSql(sql, ref Parameters);
            }
            return true;
        }

        /// <summary>
        /// 将流转记录转到历史表
        /// </summary>
        /// <param name="wiid"></param>
        /// <param name="ida"></param>
        /// <returns></returns>
        private bool ToHist(string wiid, ref Antu.DataAccess.IDataAccess ida)
        {
            //执行最后一次督办计算
            IDataParameter[] idParams = DataFactory.GetParameter(DatabaseType.Oracle, 1);
            idParams[0].ParameterName = "vId";
            idParams[0].Value = wiid;
            ida.RunProc("compute_date.compute_task", ref idParams);
            //插到历史表
            string sql = "INSERT INTO St_Work_Item_Hist(iid,wiid, prev_wiid, userid, step, accepted_time, submit_time, exusedtime, "
                        + " extotaltime, exendtime, exalarmtime, exreceivertype, exbeginmonitor, senduserid, sendstep, remark, isback,"
                        + "stepgroup,stepctlid,sendstepctlid,stepgroupctlid)"
                        + "SELECT iid,wiid, prev_wiid, userid, step, accepted_time, sysdate,exusedtime, "
                        + " extotaltime,exendtime, exalarmtime, exreceivertype, exbeginmonitor, senduserid,sendstep, remark,isback,"
                        + "stepgroup,stepctlid,sendstepctlid,stepgroupctlid  FROM ST_WORK_ITEM WHERE wiid=" + wiid;
            int rows = ida.RunSql(sql);
            if (rows > 0)
            {
                //将对应数据删除
                sql = "DELETE FROM ST_WORK_ITEM WHERE WIID=" + wiid;
                ida.RunSql(sql);
            }
            else
                return false;
            return true;
        }

        #region 创建流程实例
        /// <summary>
        /// 兼容单流向、多流向提交
        /// </summary>
        /// <param name="iid"></param>
        /// <param name="wiid"></param>
        /// <param name="fus"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public virtual bool SubmitInstance(long iid, long wiid, FlowUserStruct[] fus, ref string msg)
        {
            IDataAccess ida = dataAccess(true);
            bool isok = Sumbit(iid, wiid, fus, ref ida, ref msg);
            ida.Close(true);
            return isok;
        }
        public bool Sumbit(long iid, long wiid, FlowUserStruct[] fus, ref string msg)
        {
            IDataAccess ida = dataAccess(true);
            try
            {
                bool flag = this.Sumbit(iid, wiid, fus, ref ida, ref msg);
                if (flag)
                {
                    ida.Close(true);
                }
                else
                {
                    ida.Close(false);
                }
                return flag;
            }
            catch (Exception exception)
            {
                msg = exception.Message;
                ida.Close(false);
                return false;
            }
        }

        public bool Sumbit(long iid, long wiid, FlowUserStruct[] flowuser, ref IDataAccess ida, ref string msg)
        {
            DataTable table;
            int num;
            DataTable table2;
            string str6;
            IDataParameter[] parameter;
            string widByIID = GetWidByIID(iid.ToString());
            string sql = "SELECT * FROM ST_WORK_ITEM WHERE iid='" + iid.ToString() + "' AND wiid=" + wiid.ToString();
            ida.RunSql(sql, out table);
            string stepCtlId = table.Rows[0]["STEPCtlid"].ToString();
            if (!string.IsNullOrEmpty(table.Rows[0]["stepgroupctlid"].ToString()))
            {
                stepCtlId = table.Rows[0]["stepgroupctlid"].ToString();
            }
            StepType stepTypeByStep = GetStepTypeByStep(iid, stepCtlId);
            if (table.Rows.Count == 0)
            {
                msg = "发生异常，当前节点的数据不存在，可能是被删除了。";
                return false;
            }
            for (num = 0; num < flowuser.Length; num++)
            {
                string str4 = string.Empty;
                if (!string.IsNullOrEmpty(flowuser[num].FlowName))
                {
                    str4 = "select step_type from st_step where ctlid=(select ename from st_flow where fname ='" + flowuser[num].FlowName + "' and wid='" + widByIID + "' AND sname='" + stepCtlId + "') and wid='" + widByIID + "'";
                }
                else
                {
                    str4 = "select step_type from st_step where ctlId='" + flowuser[num].StepName + "' and wid='" + widByIID + "'";
                }
                ida.RunSql(str4, out table2);
                if (table2.Rows.Count > 0)
                {
                    string str = table2.Rows[0][0].ToString();
                    switch (str)
                    {
                        case "-2":
                        case "-3":
                        case "-4":
                            Stop(iid, wiid, "", S2Int(str), ref ida);
                            return true;
                    }
                }
            }
            sql = "SELECT type FROM ST_WORKFLOW WHERE WID='" + widByIID + "'";
            WorkflowType type2 = (WorkflowType)S2Int(dataAccess().GetValue(sql));
            if (((flowuser.Length > 1) && (stepTypeByStep != StepType.MutiFlowStart)) && (type2 == WorkflowType.ShenPi))
            {
                msg = "不是多流向的开始节点不能进行多流向提交！";
                return false;
            }
            if (flowuser[0].FlowName == "岗位内提交")
            {
                DataTable table3;
                sql = "SELECT * FROM St_Work_Item WHERE iid='" + iid.ToString() + "' AND userid='" + flowuser[0].Users[0] + "'  AND Stepctlid IN (SELECT STEPctlid FROM  ST_WORK_ITEM WHERE WIID=" + wiid.ToString() + ")";
                ida.RunSql(sql, out table3);
                DataRow row = table.Rows[0];
                str6 = row["wiid"].ToString();
                if (table3.Rows.Count > 0)
                {
                    row = table3.Rows[0];
                    str6 = row["wiid"].ToString();
                    sql = "Update St_Work_Item SET Active=1,senduserid=(SELECT USERID FROM ST_WORK_ITEM WHERE ACTIVE=1 AND WIID=" + wiid.ToString() + ") WHERE wiid=" + row["wiid"].ToString();
                    ida.RunSql(sql);
                }
                else
                {
                    bool isRound = false;
                    if (table.Rows[0]["locked"].ToString() == "2")
                    {
                        isRound = true;
                    }
                    if (!this.New(widByIID, iid.ToString(), wiid.ToString(), flowuser[0].Users[0], table.Rows[0]["Step"].ToString(), table.Rows[0]["stepctlid"].ToString(), table.Rows[0]["sendstepctlid"].ToString(), table.Rows[0]["sendstep"].ToString(), table.Rows[0]["UserId"].ToString(), "", false, ref str6, ref ida, isRound, table.Rows[0]["stepgroupctlid"].ToString(), table.Rows[0]["stepgroup"].ToString()))
                    {
                        return false;
                    }
                }
                sql = "DELETE FROM ST_WORK_ITEM_STACK WHERE Wiid=" + str6 + " OR WIID=" + row["wiid"].ToString();
                ida.RunSql(sql);
                sql = "INSERT INTO ST_WORK_ITEM_STACK(IID,WIID,PREV_WIID,USERID,STEP) VALUES(:iid,:wiid,:prev_wiid,:userid,:stepctlid)";
                parameter = DataFactory.GetParameter(DatabaseType.Oracle, 5);
                parameter[0].ParameterName = "iid";
                parameter[0].DbType = DbType.Decimal;
                parameter[0].Value = iid;
                parameter[1].ParameterName = "wiid";
                parameter[1].DbType = DbType.Decimal;
                parameter[1].Value = str6;
                parameter[2].ParameterName = "prev_wiid";
                parameter[2].DbType = DbType.Decimal;
                parameter[2].Value = row["prev_wiid"].ToString();
                parameter[3].ParameterName = "userid";
                parameter[3].DbType = DbType.String;
                parameter[3].Value = flowuser[0].Users[0];
                parameter[4].ParameterName = "stepctlid";
                parameter[4].DbType = DbType.String;
                parameter[4].Value = row["stepctlid"].ToString();
                ida.RunSql(sql, ref parameter);
            }
            else
            {
                num = 0;
                while (num < flowuser.Length)
                {
                    string str10;
                    string str7 = "";
                    if (flowuser[num].Users != null)
                    {
                        for (int j = 0; j < flowuser[num].Users.Length; j++)
                        {
                            str7 = str7 + flowuser[num].Users[j] + "','";
                        }
                        if (str7.Length > 0)
                        {
                            str7 = " AND c.UserId in ('" + str7.Substring(0, str7.Length - 2) + ")";
                        }
                    }
                    DataTable table4 = new DataTable();
                    if (!string.IsNullOrEmpty(flowuser[num].FlowName))
                    {
                        sql = "Select Distinct a.stpname,c.userid From st_group_in_step a,st_user_group c  Where  a.gid=c.gid  And EXISTS(Select ename from st_flow f Where wid=:wid And fname=:fname and f.ename=a.stpname and sname=:sname) And a.wid=:wid" + str7;
                        parameter = DataFactory.GetParameter(DatabaseType.Oracle, 3);
                        parameter[0].ParameterName = "wid";
                        parameter[0].DbType = DbType.String;
                        parameter[0].Value = widByIID;
                        parameter[1].ParameterName = "fname";
                        parameter[1].DbType = DbType.String;
                        parameter[1].Value = flowuser[num].FlowName;
                        parameter[2].ParameterName = "sname";
                        parameter[2].DbType = DbType.String;
                        parameter[2].Value = stepCtlId;
                        table4 = dataAccess().RunSql(sql, ref parameter);
                    }
                    StepType type3 = GetStepType(widByIID, flowuser[num].FlowName, stepCtlId);
                    if ((table4.Rows.Count == 0) && (type3 != StepType.End))
                    {
                        msg = "要提交的流向" + flowuser[num].FlowName + "对应的节点还没配置用户，不能提交！";
                        return false;
                    }
                    for (int i = 0; i < table4.Rows.Count; i++)
                    {
                        string stepName = table4.Rows[i]["stpname"].ToString();
                        string str9 = "";
                        sql = "SELECT extend_type,Sname From ST_Step WHERE wid=:wid AND ctlid IN(SELECT EName FROM ST_Flow WHERE wid=:wid AND FName=:fname and sname=:sname)";
                        parameter = DataFactory.GetParameter(DatabaseType.Oracle, 3);
                        parameter[0].ParameterName = "wid";
                        parameter[0].DbType = DbType.String;
                        parameter[0].Value = widByIID;
                        parameter[1].ParameterName = "fname";
                        parameter[1].DbType = DbType.String;
                        parameter[1].Value = flowuser[num].FlowName;
                        parameter[2].ParameterName = "sname";
                        parameter[2].DbType = DbType.String;
                        parameter[2].Value = stepCtlId;
                        str10 = string.Empty;
                        table2 = dataAccess().RunSql(sql, ref parameter);
                        if (table2.Rows.Count > 0)
                        {
                            str10 = table2.Rows[0]["extend_type"].ToString();
                        }
                        string step = table2.Rows[0]["Sname"].ToString();
                        if (str10 == "1")
                        {
                            stepName = flowuser[num].StepName;
                            step = flowuser[num].StepName;
                            str9 = table4.Rows[i]["stpname"].ToString();
                        }
                        string stepGroup = string.Empty;
                        if (!string.IsNullOrEmpty(str9))
                        {
                            stepGroup = table2.Rows[0]["Sname"].ToString();
                        }
                        str6 = "";
                        if (!this.New(widByIID, iid.ToString(), wiid.ToString(), table4.Rows[i]["UserId"].ToString(), step, stepName, table.Rows[0]["stepctlid"].ToString(), table.Rows[0]["step"].ToString(), table.Rows[0]["UserId"].ToString(), "", false, ref str6, ref ida, flowuser[num].IsRound, str9, stepGroup))
                        {
                            return false;
                        }
                    }
                    if (type3 == StepType.MutiFlowEnd)
                    {
                        sql = "SELECT wiid FROM ST_WORK_ITEM WHERE (ACTIVE=0 OR ACTIVE=1) AND iid=:iid AND stepctlid<>:step AND Stepctlid<>:sname";
                        parameter = DataFactory.GetParameter(DatabaseType.Oracle, 3);
                        parameter[0].ParameterName = "iid";
                        parameter[0].DbType = DbType.Decimal;
                        parameter[0].Value = iid;
                        parameter[1].ParameterName = "step";
                        parameter[1].DbType = DbType.String;
                        parameter[1].Value = table4.Rows[0]["stpname"].ToString();
                        parameter[2].ParameterName = "sname";
                        parameter[2].DbType = DbType.String;
                        parameter[2].Value = table.Rows[0]["Stepctlid"].ToString();
                        str10 = string.Empty;
                        table2 = ida.RunSql(sql, ref parameter);
                        if (table2.Rows.Count > 0)
                        {
                            str10 = table2.Rows[0][0].ToString();
                        }
                        if (string.IsNullOrEmpty(str10))
                        {
                            sql = "UPDATE ST_WORK_ITEM SET ACTIVE=0 WHERE IID='" + iid + "' AND Active=-2";
                            ida.RunSql(sql);
                        }
                        else
                        {
                            sql = "UPDATE ST_WORK_ITEM SET ACTIVE=-2 WHERE IID=:iid AND stepctlid=:step AND ACTIVE!=-1 AND Locked!=2";
                            parameter = DataFactory.GetParameter(DatabaseType.Oracle, 2);
                            parameter[0].ParameterName = "iid";
                            parameter[0].DbType = DbType.Decimal;
                            parameter[0].Value = iid;
                            parameter[1].ParameterName = "step";
                            parameter[1].DbType = DbType.String;
                            parameter[1].Value = table4.Rows[0]["stpname"].ToString();
                            ida.RunSql(sql, ref parameter);
                        }
                    }
                    sql = "DELETE FROM ST_WORK_ITEM WHERE iid=" + iid.ToString() + " AND STEPCTLID='" + table.Rows[0]["STEPCtlid"].ToString() + "' AND Active<0";
                    ida.RunSql(sql);
                    if (type3 == StepType.End)
                    {
                        sql = "SELECT wiid FROM ST_WORK_ITEM WHERE Active>-1 AND iid='" + iid.ToString() + "' AND wiid<>" + wiid.ToString();
                        if (string.IsNullOrEmpty(dataAccess().GetValue(sql)))
                        {
                            sql = "UPDATE ST_INSTANCE SET STATUS=2,finish_time=sysdate WHERE iid='" + iid.ToString() + "'";
                            ida.RunSql(sql);
                        }
                    }
                    num++;
                }
            }
            if (stepTypeByStep == StepType.MutiFlowEnd)
            {
                sql = "SELECT wiid FROM ST_WORK_ITEM WHERE IID=:iid AND UserId=:userid and Stepctlid=:step";
                parameter = DataFactory.GetParameter(DatabaseType.Oracle, 3);
                parameter[0].ParameterName = "iid";
                parameter[0].DbType = DbType.Decimal;
                parameter[0].Value = iid;
                parameter[1].ParameterName = "userid";
                parameter[1].DbType = DbType.String;
                parameter[1].Value = table.Rows[0]["UserId"].ToString();
                parameter[2].ParameterName = "step";
                parameter[2].DbType = DbType.String;
                parameter[2].Value = table.Rows[0]["stepctlid"].ToString();
                DataTable table5 = ida.RunSql(sql, ref parameter);
                for (num = 0; num < table5.Rows.Count; num++)
                {
                    if (!this.ToHist(table5.Rows[num]["wiid"].ToString(), ref ida))
                    {
                        return false;
                    }
                }
            }
            else if (!this.ToHist(wiid.ToString(), ref ida))
            {
                return false;
            }
            return true;
        }
        public bool Sumbit(long iid, long wiid, string[] flow, string[] users, ref string msg)
        {
            return Sumbit(iid, wiid, flow[0], users, ref msg);
        }
        public bool Sumbit(long iid, long wiid, string flow, string[] users, ref string msg)
        {
            FlowUserStruct[] fus = new FlowUserStruct[] { new FlowUserStruct() };
            fus[0].FlowName = flow;
            fus[0].Users = users;
            return Sumbit(iid, wiid, fus, ref msg);
        }
        public bool Sumbit(long iid, long wiid, string[] flow, string[] users, ref IDataAccess ida, ref string msg)
        {
            return Sumbit(iid, wiid, flow[0], users, ref ida, ref msg);
        }
        public bool Sumbit(long iid, long wiid, string flow, string[] users, ref IDataAccess ida, ref string msg)
        {
            FlowUserStruct[] flowuser = new FlowUserStruct[] { new FlowUserStruct() };
            flowuser[0].FlowName = flow;
            flowuser[0].Users = users;
            return Sumbit(iid, wiid, flowuser, ref ida, ref msg);
        }
        #endregion

        #region 创建流程实例
        public virtual bool CreateInstance(string wid, string userid, string ownername, ref string iid, ref string wiid)
        {
            IDataAccess ida = dataAccess(true);
            return CreateInstance(wid, userid, ownername, ref iid, ref wiid, ref ida);
        }
        public virtual bool CreateInstance(string wid, string userid, string ownername, ref string iid, ref string wiid, ref IDataAccess ida)
        {
            //_FlowFactoryProvider workflow = new _FlowFactoryProvider();
            string errMsg = "";
            try
            {
                if (false == NewInstance(wid, userid, ownername, ref iid, ref wiid, ref errMsg, ref ida))
                {
                    Antu.Utilities.UtilitiesPage.AlertDialog("受理失败！\r\n原因：" + errMsg + "", "history.back();");
                    ida.Close(false);
                    return false;
                }
                else
                {
                    ida.Close(true);
                    return true;
                }
            }
            catch (Exception ex)
            {
                Antu.Utilities.UtilitiesPage.AlertDialog("受理失败！\r\n原因：" + errMsg + "", "history.back();");
                ida.Close(false);
                return false;
            }
        }
        /// <summary>
        /// 中止流程
        /// </summary>
        /// <param name="iids"></param>
        /// <returns></returns>
        public virtual bool StopInstance(string iid, string wiid, string userid, string remark)
        {
            IDataAccess ida = dataAccess(true);
            bool isok = Stop(long.Parse(iid), long.Parse(wiid), remark + "  终止人：" + GetUserName(userid), ref ida);
            ida.Close(true);
            return isok;
        }
        /// <summary>
        /// 删除流程
        /// </summary>
        /// <param name="iids">如('111')</param>
        /// <returns></returns>
        public virtual bool DeleteInstance(string iids)
        {
            string sql = "";
            DataTable dt;
            IDataAccess ida = dataAccess(true);
            try
            {
                sql = "select res_value from st_dynamic_resource where type=2 and iid in " + iids;
                dataAccess().RunSql(sql, out dt);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    sql = "delete from st_attachment where aid='" + System.Convert.ToString(dt.Rows[i]["res_value"]) + "'";
                    ida.RunSql(sql);
                }
                sql = "delete from st_dynamic_resource where iid in " + iids;
                ida.RunSql(sql);
                sql = "delete from st_work_item where iid in " + iids;
                ida.RunSql(sql);
                sql = "delete from st_suspend where iid in " + iids;
                ida.RunSql(sql);
                sql = "delete from st_work_item_hist where iid in " + iids;
                ida.RunSql(sql);
                sql = "delete from st_work_item_stack where iid in " + iids;
                ida.RunSql(sql);
                sql = "delete from xt_instance_ext where iid in " + iids;
                ida.RunSql(sql);
                sql = "delete from xt_work_item_extend where iid in " + iids;
                ida.RunSql(sql);

                //一定要放在最后面
                sql = "delete from st_instance where iid in " + iids;
                ida.RunSql(sql);
                ida.Close(true);
                return true;
            }
            catch (Exception ex)
            {
                ida.Close(false);
                return false;
            }
        }
        #endregion


        #region 12-31
        /// <summary>
        /// 获取部门名称
        /// </summary>
        /// <returns></returns>
        public DataTable GetDepartName()
        {
            string sql = "select depart_name from st_department where parent_id=0";
            DataTable dt = null;
            dataAccess().RunSql(sql, out dt);
            return dt;
        }
        /// <summary>
        /// 由单位名称获取部门名称
        /// </summary>
        /// <param name="dwName"></param>
        /// <returns></returns>
        public DataTable GetDepartNameByDName(string dwName)
        {
            DataTable dt = null;
            string sql = "select depart_name from st_department where parent_id in(select departid from st_department where depart_name like '" + dwName + "')";
            dataAccess().RunSql(sql, out dt);
            return dt;
        }

        /// <summary>
        /// 删除表中数据
        /// </summary>
        /// <returns></returns>
        public virtual bool DeleteTableRow(string tablename, string columnname, string value)
        {
            string sql = "delete from " + tablename + " where " + columnname + "='" + value + "' ";
            return dataAccess().RunSql(sql) > 0 ? true : false;
        }

        public string GetUserName(string UserID)
        {
            string userName = string.Empty;
            string sql = "select user_name from st_user where userid='" + UserID + "' ";
            return userName = dataAccess().GetValue(sql);
        }
        public string GetWorkItemStepName(string WIID)
        {
            string stepName = string.Empty;
            string sql = "select step from st_work_item where wiid='" + WIID + "' ";
            return stepName = dataAccess().GetValue(sql);
        }
        public DataTable GetIFGQ(string strid, string wiid)
        {
            DataTable dt = null;
            string sql = string.Format(@"select suspend_type,memo,id from xt_suspend_apply where serial='{0}' 
        and handleid='{1}' and ischeckup is null and isdelete is null", strid, wiid);
            dataAccess().RunSql(sql, out dt);
            return dt;
        }
        public string GetGQValue(string step, string strid, string userid, string wname)
        {
            string sql = string.Empty;
            string strString = string.Empty;
            if (step == "1")
            {
                sql = string.Format("select wname from st_workflow where wid=(select wid from  st_instance where iid='{0}')", strid);
                strString = dataAccess().GetValue(sql);
            }
            else if (step == "2")
            {
                sql = string.Format("select f.wid from  st_instance f where f.iid='" + strid + "'");
                strString = dataAccess().GetValue(sql);
            }
            else if (step == "3")
            {
                sql = string.Format(@"select distinct a.FLOW_NO from XT_SUSPEND_FLOW a,XT_SUSPENDFLOW_USERID b where a.ID=b.FLOW_ID 
and b.USERID='{0}' and b.serial_id in( select wid from st_workflow where wname='{1}')", userid, wname);
                string curUserStepNo = dataAccess().GetValue(sql);
                sql = string.Format(@"select min(a.flow_no) from xt_suspend_flow a where a.id in
            (select t.flow_id from xt_suspendflow_userid t where t.serial_id in( select wid from st_workflow where wname= '{0}')) and a.flow_no>'{1}'",
                    wname, curUserStepNo);
                strString = dataAccess().GetValue(sql);
            } return strString;
        }
        public DataTable GetWorkItemDT(string iid, string wiid)
        {
            string sql = string.Format(@"select i.name,w.wname,wi.iid,wi.wiid from st_workflow w,st_instance i,st_work_item wi 
where w.wid=i.wid and i.iid=wi.iid and wi.iid='{0}' and wi.wiid='{1}' and wi.active>=0", iid, wiid);
            DataTable dt = null;
            dataAccess().RunSql(sql, out dt);
            return dt;
        }
        /// <summary>
        /// 申请挂起
        /// </summary>
        public bool ApplySuspend(string iid, string wiid, string userid, string memo)
        {
            bool result = false;
            try
            {
                string sql = string.Format("select count(*) from st_work_item where iid='{0}' and wiid='{1}' and active>=0", iid, wiid);
                if (dataAccess().GetValue(sql) == "1")
                {
                    sql = string.Format("insert into XT_SUSPEND_APPLY(id,SERIAL,HANDLEID,USERID,APPLY_TIME,MEMO)  values(SS_ST_SUSPEND_ID.Nextval,'{0}','{1}','{2}',sysdate,'{3}')", iid, wiid, userid, memo);
                    dataAccess().RunSql(sql);
                    result = true;
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                dataAccess().Close();
            }
            return result;
        }
        /// <summary>
        /// 取消申请挂起
        /// </summary>
        public bool CancelApplySuspend(string iid, string wiid, string userid)
        {
            bool result = false;

            try
            {
                string sql = string.Format("update xt_suspend_apply set isdelete='1' ,checkuserid='{0}',delete_time=sysdate where serial='{1}' and handleid='{2}' and delete_time is null", userid, iid, wiid);
                dataAccess().RunSql(sql);
                result = true;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                dataAccess().Close();
            }
            return result;
        }

        /// <summary>
        /// 获取申请挂起列表
        /// </summary>
        /// <returns></returns>
        public DataTable GetApplySuspendData(string iid, string wname, string name)
        {

            DataTable dt;
            try
            {
                string sql = "select *  from (select x.*,   i.name,   (select wname from st_workflow where wid = i.wid) wname,   (select user_name from st_user where userid = x.userid) username  from xt_suspend_apply x, st_instance i where x.isdelete is null   and x.ischeckup is null   and x.serial = i.iid)  where 1 = 1";
                sql += iid == "" ? "" : " and serial like '%" + iid + "%'";
                sql += wname == "" ? "" : " and wname like '%" + wname + "%'";
                sql += name == "" ? "" : " and name like '%" + name + "%'";
                sql += " order by apply_time desc";
                dataAccess().RunSql(sql, out dt);
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                dataAccess().Close();
            }
            return dt;
        }

        /// <summary>
        /// 获取申请挂起流程名称
        /// </summary>
        public DataTable GetApplySuspendWame(string wnames)
        {


            DataTable dt;
            try
            {
                string sql = "select distinct * from (select (select distinct wname from st_workflow where wid = i.wid) wname  from st_instance i, st_work_item wi, xt_suspend_apply x  where i.iid = wi.iid and i.iid = x.serial and i.status=1 and wi.wiid = x.handleid and x.ischeckup is null and x.isdelete is null) where wname in(" + wnames + ")";
                dataAccess().RunSql(sql, out dt);
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                dataAccess().Close();
            }
            return dt;
        }

        /// <summary>
        /// 获取已确认挂起的流程名称 确认人
        /// </summary>
        /// <returns></returns>
        public DataTable GetSuspendListWname(string userid, string wnames)
        {

            DataTable dt;
            try
            {
                string sql = "select distinct w.wname  from st_workflow w, st_instance i, st_suspend s  where w.wid = i.wid and i.iid = s.iid";
                sql += string.Format(" and w.wname in({0})", wnames);
                //sql += string.Format(" and s.userid='{0}'", userid);
                dataAccess().RunSql(sql, out dt);
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                dataAccess().Close();
            }
            return dt;
        }

        /// <summary>
        /// 获取已确认挂起的流程名称 申请挂起者
        /// </summary>
        /// <returns></returns>
        public DataTable GetSuspendWname(string userid)
        {

            DataTable dt;
            try
            {
                string sql = "select distinct w.wname  from st_workflow w, st_instance i, st_suspend s  where w.wid = i.wid and i.iid = s.iid and s.end_date is null and s.end_userid is null";
                sql += userid == "" ? "" : " and s.apply_id in( select wiid from st_work_item wi where userid='" + userid + "' and active>=0 ) ";
                dataAccess().RunSql(sql, out dt);
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                dataAccess().Close();
            }
            return dt;
        }


        /// <summary>
        /// 挂起流程
        /// </summary>
        /// <returns></returns>
        public bool SuspendWorkFlow(string iid, string wiid, string userid, string description)
        {
            bool result = false;
            IDataAccess ida = dataAccess(true);
            try
            {
                string sql = string.Format("select count(*) from st_work_item  where iid='{0}' and wiid='{1}' and active>=0", iid, wiid);
                if (dataAccess().GetValue(sql) == "1")
                {
                    string ssql = string.Format("insert into st_suspend  (iid, start_date, userid, description, apply_id) values  ('{0}', sysdate, '{1}', '{2}', '{3}')", iid, userid, description, wiid);
                    ida.RunSql(ssql);
                    string tsql = string.Format("update XT_SUSPEND_APPLY set CHECKUSERID = '{0}', CHECK_TIME = sysdate, ISCHECKUP = 1  where serial = '{1}' and HANDLEID = '{2}' and ISCHECKUP is null", userid, iid, wiid);
                    ida.RunSql(tsql);
                    ida.Close(true);
                    result = true;
                }
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                ida.Close(false);
            }
            return result;
        }

        /// <summary>
        /// 取消挂起
        /// </summary>
        public bool CancelSuspend(string iid, string userid)
        {
            IDataAccess ida = dataAccess(true);
            try
            {
                string sql = string.Format("update st_suspend set end_date = sysdate,end_userid = '{0}' where iid = '{1}' and end_date is null", userid, iid);
                ida.RunSql(sql);
                string ssql = string.Format("update xt_suspend_apply set isdelete='1' ,checkuserid='{0}',delete_time=sysdate where serial='{1}' and delete_time is null", userid, iid);
                ida.RunSql(ssql);
                ida.Close(true);
                return true;
            }
            catch (Exception e)
            {
                ida.Close(false);
                throw new Exception(e.Message);
            }

        }

        /// <summary>
        /// 按ID获取申请挂起信息
        /// </summary>
        public DataTable GetApplySuspendDataByID(string id)
        {

            DataTable dt;
            try
            {
                string sql = string.Format("select * from xt_suspend_apply where id='{0}'", id);
                dataAccess().RunSql(sql, out dt);

            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                dataAccess().Close();
            }
            return dt;
        }
        /// <summary>
        /// 确认人获取已确认挂起列表
        /// </summary>
        /// <returns></returns>
        public DataTable GetSuspendListByUser(string iid, string name, string wname, string userid)
        {

            DataTable dt;
            try
            {
                string sql = "select s.id,s.iid, w.wname, i.name, s.description, s.start_date, s.end_date  from st_workflow w, st_instance i, st_suspend s  where w.wid = i.wid and i.iid = s.iid";
                sql += iid == "" ? "" : " and i.iid like'%" + iid + "%'";
                sql += name == "" ? "" : " and i.name like'%" + name + "%'";
                sql += wname == "" ? "" : " and w.wname like'%" + wname + "%'";
                sql += userid == "" ? "" : " and s.userid='" + userid + "'";
                sql += " order by  s.start_date desc";
                dataAccess().RunSql(sql, out dt);
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                dataAccess().Close();
            }
            return dt;
        }

        /// <summary>
        /// 申请挂起者获取已确认挂起列表
        /// </summary>
        public DataTable GetSuspendList(string iid, string name, string wname, string userid)
        {
            DataTable dt;
            try
            {
                string sql = "select  s.iid,w.wname,i.name,s.description, s.start_date  from st_workflow w, st_instance i, st_suspend s  where w.wid = i.wid and i.iid = s.iid and s.end_date is null and s.end_userid is null";
                sql += iid == "" ? "" : " and i.iid like'%" + iid + "%'";
                sql += name == "" ? "" : " and i.name like'%" + name + "%'";
                sql += wname == "" ? "" : " and w.wname like'%" + wname + "%'";
                sql += userid == "" ? "" : " and s.apply_id in( select wiid from st_work_item wi where userid='" + userid + "' and active>=0 )";
                sql += " order by  s.start_date desc";
                dataAccess().RunSql(sql, out dt);
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                dataAccess().Close();
            }
            return dt;
        }
        /// <summary>
        /// 申请挂起者获取已确认挂起列表
        /// </summary>
        public DataTable GetSuspendLists(string iid, string name, string wname, string userid)
        {
            DataTable dt;
            try
            {
                string sql = @"select  s.iid,w.wname,i.name,s.description, s.start_date,(select user_name from st_user where st_user.userid in (select userid from xt_suspend_apply  
    where xt_suspend_apply.serial=i.iid and handleid = s.apply_id) and rownum <= 1) 申请挂起人,
    (select user_name from st_user where st_user.userid in 
    ( select userid   from st_suspend  where st_suspend.iid = i.iid and end_userid is null and rownum<=1)) 确认挂起人  from st_workflow w, st_instance i, st_suspend s  where w.wid = i.wid and i.iid = s.iid and s.end_date is null and s.end_userid is null";
                sql += iid == "" ? "" : " and i.iid like'%" + iid + "%'";
                sql += name == "" ? "" : " and i.name like'%" + name + "%'";
                sql += wname == "" ? "" : " and w.wname like'%" + wname + "%'";
                sql += userid == "" ? "" : " and s.apply_id in(select wiid from st_work_item wi where iid in ( select iid from st_work_item wi where userid='" + userid + "'  and active>=0 )  and active>=0 )    ";
                sql += " order by  s.start_date desc";
                dataAccess().RunSql(sql, out dt);
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                dataAccess().Close();
            }
            return dt;
        }
        /// <summary>
        /// 申请挂起者获取已确认挂起列表的数目
        /// </summary>
        public int GetSuspendFlowCount(string userid)
        {
            int result = 0;

            try
            {
                string sql = string.Format("select count(distinct s.iid)  from st_workflow w, st_instance i, st_suspend s  where w.wid = i.wid and i.iid = s.iid and s.end_date is null and s.end_userid is null and s.apply_id in (select wiid  from st_work_item wi  where userid = '{0}'   and active >= 0)", userid);
                result = int.Parse(dataAccess().GetValue(sql));
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                dataAccess().Close();
            }
            return result;
        }

        /// <summary>
        /// 验证业务是否挂起
        /// </summary>
        public bool IsSuspendByIID(string iid)
        {
            bool result = false;

            try
            {
                string sql = string.Format("select count(*)  from st_suspend s  where end_date is null and end_userid is null and iid='{0}' and iid in(select iid from st_instance where status=1)", iid);
                result = int.Parse(dataAccess().GetValue(sql)) > 0;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                dataAccess().Close();
            }
            return result;
        }
        /// <summary>
        /// 执行存储过程，计算办理时间，更新表EXUSEDTIME字段
        /// </summary>
        public void UpdateWorkTime(string iid)
        {
            try
            {
                IDataParameter[] idParams = Antu.DataAccess.DataFactory.GetParameter(Antu.DataAccess.DatabaseType.Oracle, 1);
                idParams[0].ParameterName = "inIID";
                idParams[0].Value = iid;
                dataAccess().RunProc("ANTU_NJGT_BUSINESS.UpdateWorkID", ref idParams);

            }
            catch (Exception eRunPro)
            {
                throw new Exception("执行获取工作时间存储过程出错！" + eRunPro.Message);
            }
            finally
            {
                dataAccess().Close();
            }
        }
        /// <summary>
        /// 计算流程办理总的时间
        /// </summary>
        /// <param name="iid"></param>
        /// <param name="ida"></param>
        /// <returns></returns>
        public string getTotalTime(string iid)
        {

            string _totaltime = string.Empty;
            try
            {
                string sql = string.Format("select EXUSEDTIME from st_instance where iid={0} and rownum<=1", iid);
                string result = dataAccess().GetValue(sql);
                if (!string.IsNullOrEmpty(result) && result != "0")
                {
                    _totaltime = GetZWTime(result);
                }
            }
            catch (Exception exerr)
            {
                throw new Exception(exerr.Message);
            }
            finally
            {
                dataAccess().Close();
            }
            return _totaltime;
        }/// <summary>
        /// 秒换算成时间
        /// </summary>
        /// <param name="strSeconds"></param>
        /// <returns></returns>
        public string GetZWTime(string strSeconds)
        {
            long _seconds;
            bool flag = long.TryParse(strSeconds, out _seconds);
            if (flag)
            {
                string days = "";
                string hours = "";
                string minutes = "";
                string seconds = "";
                if ((_seconds / 25200) > 0)
                {
                    days = Convert.ToString(_seconds / 25200) + "天";
                }
                if ((_seconds % 25200) / 3600 > 0)
                {
                    hours = Convert.ToString((_seconds % 25200) / 3600) + "小时";
                }
                if (((_seconds % 25200) % 3600) / 60 > 0)
                {
                    minutes = Convert.ToString(((_seconds % 25200) % 3600) / 60) + "分";
                }
                if (Convert.ToInt32(((_seconds % 25200) % 3600) % 60) > 0)
                {
                    seconds = Convert.ToString(((_seconds % 25200) % 3600) % 60) + "秒";
                }

                if (seconds != "0秒")
                {
                    return days + hours + minutes + seconds;
                }
                else
                {
                    return days + hours + minutes;
                }
            }
            else
            {
                return "";
            }
        }
        /// <summary>
        /// 获取是否挂起
        /// </summary>
        /// <param name="iid"></param>
        /// <returns></returns>
        public string getSuspend(string iid)
        {
            string strsql = "select (select user_name  from st_user  where userid in ((select userid   from xt_suspend_apply x  where serial = s.iid   and handleid = s.apply_id))) user_name,   s.start_date,   s.description  from st_instance i, st_suspend s  where i.iid = s.iid and i.status=1 and s.end_date is null and s.end_userid is null and  s.iid=" + iid + " and rownum <= 1 ";
            DataTable dt = new DataTable();
            string status = string.Empty;
            try
            {
                dataAccess().RunSql(strsql, out dt);
                if (dt.Rows.Count > 0)
                {
                    status = "<a href=\"#\" onclick=\"javascript:alert('" + dt.Rows[0]["DESCRIPTION"].ToString() + "')\">" + dt.Rows[0]["user_name"].ToString() + "于" + dt.Rows[0]["start_date"].ToString() + "挂起</a>";
                }
                else
                {
                    status = "否";
                }
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                dataAccess().Close();
            }
            return status;
        }
        /// <summary>
        /// 查看流程信息
        /// </summary>
        /// <param name="serial"></param>
        /// <returns></returns>
        public DataTable GetViewMis(string serial)
        {
            DataTable dt;
            string sql = string.Format(@"select iid,   wiid,   active,   step,   (select user_name from st_user u where u.userid = s.userid) notes,
sndtime,   passday  from (select wi.iid,   wi.wiid,   wi.active,   wi.step,   wi.userid,   wi.accepted_time,   
null sndtime,   wi.exusedtime passday  from st_work_item wi  where iid = '{0}'   and wi.active >= 0   union   
select wh.iid,   wh.wiid,   -1 active,   wh.step,   wh.userid,   wh.accepted_time,   wh.submit_time sndtime,   
wh.exusedtime passday  from st_work_item_hist wh  where iid = '{0}') s  where iid in (select iid from st_instance)  order by wiid", serial);
            dataAccess().RunSql(sql, out dt);
            return dt;
        }
        /// <summary>
        /// 查看案件办结情况
        /// </summary>
        /// <param name="iid"></param>
        /// <returns></returns>
        public DataTable GetCaseTake(string iid)
        {
            DataTable dt;
            string strsql = string.Format("select a.wname,b.status from st_workflow a,st_instance b where a.wid=b.wid and b.iid={0}", iid);
            dataAccess().RunSql(strsql, out dt);
            return dt;
        }
        /// <summary>
        /// 获取挂起人员的ID
        /// </summary>
        /// <param name="serial"></param>
        /// <returns></returns>
        public string GetSuUser(string serial)
        {
            string uid = string.Empty;
            string sql = " select userid from  xt_suspend_apply where serial='" + serial + "' and delete_time is null and rownum<=1";
            return uid = dataAccess().GetValue(sql);
        }
        /// <summary>
        /// 由用户ID获取不同流程名称
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public DataTable GetWnameByUserId(string userId, string wnames)
        {
            DataTable dt;
            string sql = @"Select Distinct wname From st_workflow Where wname in(" + wnames + @") and wid In(Select wid From st_group_in_step Where
gid In(Select gid From st_user_group Where userid='" + userId + "'))";
            dataAccess().RunSql(sql, out dt);
            return dt;
        }
        /// <summary>
        /// 获取权利委托数据
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public DataTable GetDelegate(string userId, string sqlwhere)
        {
            DataTable dt;
            string sql = @"SELECT a.Id,b.user_name username,c.user_name delegate_UserName, delegate_task, delegate_time, begin_time,end_time 
FROM st_delegate a,st_user b,st_user c WHERE a.userid=b.userid and a.delegate_userid=c.userid AND a.USERID='" + userId + "'";
            if (!string.IsNullOrEmpty(sqlwhere))
            {
                sql += sqlwhere;
            }
            dataAccess().RunSql(sql, out dt);
            return dt;
        }
        /// <summary>
        /// 根据userid获取所属部门列表
        /// </summary>
        /// <param name="strUserId"></param>
        /// <returns></returns>
        public DataTable GetDepListByUserid(string strUserId)
        {
            String strSql = string.Format(@"select * from st_department a where departid in 
                (select order_id from st_user_department where userid='{0}') order by a.parent_id ", strUserId);
            DataTable dt;
            dataAccess().RunSql(strSql, out dt);
            return dt;
        }
        /// <summary>
        /// 获得部门下的科室(包含与部门名称重复的科室)
        /// </summary>
        /// <param name="orderId">Order_Id</param>
        /// <returns></returns>
        public DataTable GetDepartment(string parent_id)
        {
            if (String.IsNullOrEmpty(parent_id))
            {
                parent_id = "0";
            }
            DataTable rs;
            string whereExt = @"and exists (select 1 from st_user_department s, st_user u
                                            where s.userid = u.userid and u.invalid ='1' and s.order_id = t.departid)";
            string sql = string.Format(@"select * from ST_DEPARTMENT t where PARENT_ID ='{0}' {1} order by order_id", parent_id, whereExt);
            dataAccess().RunSql(sql, out rs);

            if (rs.Rows.Count == 0)
            {
                sql = string.Format("select * from ST_DEPARTMENT where PARENT_ID ='{0}' order by order_id", parent_id);
                dataAccess().RunSql(sql, out rs);
            }
            return rs;
        }
        /// <summary>
        /// 获取部门与人员数据对应的人员信息
        /// </summary>
        /// <param name="depId"></param>
        /// <returns></returns>
        public DataTable GetBindUser(string depId)
        {
            DataTable dt;
            string sql = @"select b.userid,b.user_name from st_user_department a,st_user b Where a.userid=b.userid And a.order_id=(select parent_id from st_department where departid = '" + depId + "')";
            dataAccess().RunSql(sql, out dt);
            return dt;
        }
        /// <summary>
        /// 验证是否存在委托
        /// </summary>
        /// <param name="UserId"></param>
        /// <returns></returns>
        public bool CheckDelegateByUserId(string UserId, string flowtype)
        {
            string strSql = string.Format(@"select userid from st_delegate where userid='{0}' And delegate_task ='{1}' 
And Begin_Time <= Sysdate And end_time >= Sysdate  ", UserId, flowtype);
            string strGetUserId = dataAccess().GetValue(strSql);
            if (string.IsNullOrEmpty(strGetUserId))
            {
                return true;
            }
            else { return false; }
        }
        /// <summary>
        /// 新增委托
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="UserName"></param>
        /// <param name="ProcessName"></param>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        public void InsertNewDelegate(string userId, string UserName, string ProcessName, string startDate, string endDate)
        {
            string sql = String.Format(@"INSERT INTO st_delegate(userid,delegate_userid, delegate_task, delegate_time,  begin_time, end_time)
VALUES('{0}','{1}','{2}',sysdate,to_date('{3}','YYYY-MM-DD HH24:Mi:SS'),to_date('{4}','YYYY-MM-DD HH24:Mi:SS'))",
             userId, UserName, ProcessName, startDate, endDate);
            dataAccess().RunSql(sql);
        }
        /// <summary>
        /// 获取部门列表
        /// </summary>
        /// <returns></returns>
        public DataTable GetDepartMentList()
        {
            DataTable dt;
            string sql = "select * from st_department where 1=1 ";
            dataAccess().RunSql(sql, out dt);
            return dt;
        }
        /// <summary>
        /// 由给定表获取指定数据 
        /// </summary>
        /// <param name="table"></param>
        /// <param name="column"></param>
        /// <param name="vcolumn"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public string GetValueByn(string table, string column, string vcolumn, string value)
        {
            string gvalue = string.Empty;
            string sql = "select " + column + " from " + table + " where " + vcolumn + "='" + value + "'";
            return gvalue = dataAccess().GetValue(sql);
        }
        /// <summary>
        /// 保存部门信息
        /// </summary>
        public void SaveDepartMent(string departID, string bmmc, string sjbm, string orderId, string parentId)
        {
            string sql = string.Empty;
            if (!string.IsNullOrEmpty(departID))
            {
                sql = "update st_department set depart_name='" + bmmc + "',parent_id='" + sjbm + "',order_id='"
                        + orderId + "',OPERATION_FLAG=2  where departid ='" + departID + "'";
                if (!string.IsNullOrEmpty(parentId) && parentId == "0" && string.IsNullOrEmpty(sjbm))
                {
                    sql = "update st_department set depart_name='" + bmmc + "',parent_id='" + parentId + "',order_id='"
                        + orderId + "',OPERATION_FLAG=2  where departid ='" + departID + "'";
                }
                else if (string.IsNullOrEmpty(parentId) && !string.IsNullOrEmpty(sjbm) && sjbm == "0")
                {
                    sql = "update st_department set depart_name='" + bmmc + "',parent_id=null,order_id='"
                        + orderId + "',OPERATION_FLAG=2  where departid ='" + departID + "'";
                }
                dataAccess().RunSql(sql);
            }
        }
        public int GetDptmentByPId(string parentId)
        {
            int tt = 0;
            string sql = "select count(*) from st_department where parent_id='" + parentId + "'";
            return tt = Convert.ToInt32(dataAccess().GetValue(sql));
        }
        /// <summary>
        /// 获取所有部门信息
        /// </summary>
        /// <returns></returns>
        public DataTable GetAllDeptInfo()
        {
            String sql = "select * from ST_DEPARTMENT order by order_id";
            DataTable dtOut;
            dataAccess().RunSql(sql, out dtOut);
            return dtOut;
        }
        /// <summary>
        /// 获取有操作权限的部门
        /// </summary>
        /// <returns></returns>
        public DataTable GetHasRightDep(string strDepName)
        {
            string strSql = "select * from st_department where parent_id=(select parent_id from st_department "
            + "where depart_name='" + strDepName + "') order by order_id ";
            DataTable dt;
            dataAccess().RunSql(strSql, out dt);
            return dt;
        }
        /// <summary>
        /// 向上或向下移动部门
        /// </summary>
        /// <param name="strDepId">部门主键</param>
        /// <param name="strWay">移动方向</param>
        public void MoveDepart(string strDepId, string strWay)
        {
            IDataAccess ida = dataAccess(true);
            try
            {
                string strSql = "select * from st_department where parent_id=(select parent_id from st_department "
            + "where departid='" + strDepId + "') order by order_id ";
                DataTable dtTemp = new DataTable();
                dataAccess().RunSql(strSql, out dtTemp);
                int pos = 0;
                for (int i = 0; i < dtTemp.Rows.Count; i++)
                {
                    if (strDepId == dtTemp.Rows[i]["departid"].ToString())
                    {
                        pos = i;
                        break;
                    }
                }
                string ThisId = dtTemp.Rows[pos]["departid"].ToString(); //要移动部门主键
                string ThisOrderId = dtTemp.Rows[pos]["order_id"].ToString(); //要移动部门order_id
                if (strWay.ToLower() == "up" && pos > 0)  //上移
                {
                    string PreId = dtTemp.Rows[pos - 1]["departid"].ToString(); //上一条记录主键
                    string PreOrderId = dtTemp.Rows[pos - 1]["order_id"].ToString(); //上一条记录order_id
                    strSql = "update st_department set order_id='" + PreOrderId + "' where departid='" + ThisId + "'";
                    ida.RunSql(strSql);
                    strSql = "update st_department set order_id='" + ThisOrderId + "' where departid='" + PreId + "'";
                    ida.RunSql(strSql);
                    //更新下级部门
                    strSql = "update st_department set order_id ='" + PreOrderId + "'||substr(order_id," + (PreOrderId.Length + 1) + ") "
                    + "where parent_id='" + ThisId + "'";
                    ida.RunSql(strSql);
                    strSql = "update st_department set order_id ='" + ThisOrderId + "'||substr(order_id," + (ThisOrderId.Length + 1) + ") "
                    + "where parent_id='" + PreId + "'";
                    ida.RunSql(strSql);
                }
                else if (strWay.ToLower() == "down" && pos < dtTemp.Rows.Count - 1)  //下移
                {
                    string NextId = dtTemp.Rows[pos + 1]["departid"].ToString(); //下一条记录主键
                    string NextOrderId = dtTemp.Rows[pos + 1]["order_id"].ToString(); //上一条记录order_id

                    strSql = "update st_department set order_id='" + NextOrderId + "' where departid='" + ThisId + "'";
                    ida.RunSql(strSql);
                    strSql = "update st_department set order_id='" + ThisOrderId + "' where departid='" + NextId + "'";
                    ida.RunSql(strSql);
                    //更新下级部门
                    strSql = "update st_department set order_id ='" + NextOrderId + "'||substr(order_id," + (NextOrderId.Length + 1) + ") "
                    + "where parent_id='" + ThisId + "'";
                    ida.RunSql(strSql);
                    strSql = "update st_department set order_id ='" + ThisOrderId + "'||substr(order_id," + (ThisOrderId.Length + 1) + ") "
                    + "where parent_id='" + NextId + "'";
                    ida.RunSql(strSql);
                }
                ida.Close(true);
            }
            catch (Exception)
            {
                ida.Close(false);
                throw;
            }

        }
        public DataTable GetDptIdByUserId(string userId)
        {
            DataTable dt;
            string sql = "select order_id from st_user_department where userid='" + userId + "'";
            dataAccess().RunSql(sql, out dt);
            return dt;
        }
        /// <summary>
        /// 获取上级部门
        /// </summary>
        /// <returns></returns>
        public DataTable GetYJBM()
        {
            string sql = "select departid,depart_name from st_department where parent_id=0";
            DataTable dt;
            dataAccess().RunSql(sql, out dt);
            return dt;
        }
        public string GetNewDptId()
        {
            string sql = "select nvl(max(DEPARTID),0)+1 from st_department";
            return dataAccess().GetValue(sql);
        }

        public void InsertDepartment(string dptId, string bmmc, string sjbm, string ordId, string parentId)
        {
            string sql = "insert into st_department(departid,depart_name, parent_id, order_id,OPERATION_FLAG) VALUES('" + dptId
                    + "','" + bmmc + "','" + sjbm + "','" + ordId + "',1)";
            if (!string.IsNullOrEmpty(parentId) && string.IsNullOrEmpty(sjbm))
            {
                sql = "insert into st_department(departid,depart_name, parent_id, order_id,OPERATION_FLAG) VALUES('" + dptId
                    + "','" + bmmc + "','" + parentId + "','" + ordId + "',1)";
            }
            dataAccess().RunSql(sql);
        }
        public string GetOrdIdByDptId(string dptId)
        {
            string sql = "select order_id from st_department where departid='" + dptId + "'";
            return dataAccess().GetValue(sql);
        }
        public string GetOrdIdByordId(string ordId)
        {
            string sql = "select order_id from st_department where order_id='" + ordId + "'";
            return dataAccess().GetValue(sql);
        }
        public void UpdateOrderId(string orderId, string oldorderId)
        {
            string sql = string.Format(@"update st_department set  order_id=replace(order_id,'{1}','{0}'),OPERATION_FLAG=2,PARENT_ID=null 
                            where order_id like '{1}%'", orderId, oldorderId);
            dataAccess().RunSql(sql);
        }
        /// <summary>
        /// 获取新插入部门的上级部的ID号和该部门的order_id
        /// </summary>
        /// <returns></returns>
        public string GetOrderId(string bmid, string sjbm, string ordId)
        {
            string id = bmid;//部门ID
            string sql = "SELECT parent_id FROM ST_DEPARTMENT WHERE DepartId='" + id + "'";
            if (!String.IsNullOrEmpty(id) && sjbm.Trim() == dataAccess().GetValue(sql))//如果上级部门没有变，则OrderID也不变
            {
                return ordId;
            }
            else//新增时或上级部门发生了变化
            {
                string orderId = "";
                if (sjbm == "0")//顶层
                {
                    sql = "SELECT Max(ORDER_ID)+1 FROM St_Department where parent_id=0";
                    orderId = dataAccess().GetValue(sql);
                }
                else
                {
                    sql = "SELECT ORDER_ID FROM ST_DEPARTMENT WHERE DepartId='" + sjbm + "'";
                    string parentOrderId = dataAccess().GetValue(sql);
                    sql = "select trim(to_char(Max(to_number(SUBSTR(ORDER_ID,Length('" + parentOrderId + "')+1)))+1,'000')) from st_department t Where Parent_id=" + parentOrderId;
                    string tmp = dataAccess().GetValue(sql);
                    if (String.IsNullOrEmpty(tmp.Trim()))
                    {
                        sql = string.Format("select max(order_id)+1 from st_department where parent_id='{0}'", sjbm);
                        orderId = dataAccess().GetValue(sql);
                    }
                    else
                    {
                        orderId = parentOrderId + tmp;
                    }
                }
                return orderId;
            }
        }
        public void InsertUserDpt(string userid, string orderid)
        {
            string sql = string.Format("insert into st_user_department (userid, order_id) values ('{0}','{1}')", userid, orderid);
            dataAccess().RunSql(sql);
        }
        public void InsertUserGpt(string userid, string gid)
        {
            string sql = string.Format("insert into st_user_group (userid, GID) values ('{0}','{1}')", userid, gid);
            dataAccess().RunSql(sql);
        }
        public void UpUserOrdIdByUId(string userid, string orderbyid)
        {
            string sql = "update st_user set orderbyid='" + orderbyid + "',OPERATION_FLAG=3  where userid='" + userid + "'";
            dataAccess().RunSql(sql);
        }
        public DataTable GetDepartMentUser(string bmid, string sjbm, string quecon)
        {
            string strWhere = " and order_id ='" + bmid + "'";
            if (!string.IsNullOrEmpty(sjbm) && sjbm != "0") { strWhere += " and order_id in (select departid from st_department where departid='" + sjbm + "' or parent_id ='" + sjbm + "')"; }
            string strSql = string.Format(@"SELECT (case when (select count(*) from st_user_department where userid = t.userid {0})>0
then 1 else 0 end) checked, UserId, login_name, User_Name, orderbyid FROM ST_USER t WHERE 1 = 1 ", strWhere);
            if (!string.IsNullOrEmpty(quecon)) { strSql += " and USER_NAME like '%" + quecon + "%' or LOGIN_NAME like '%" + quecon + "%'"; }
            strSql += " order by checked desc, orderbyid asc";
            DataTable dt;
            dataAccess().RunSql(strSql, out dt);
            return dt;
        }
        public string GetOneStrString(string table, string column, string vcolumn, string value)
        {
            string sql = "select " + column + " from " + table + " where " + vcolumn + "='" + value + "'";
            return dataAccess().GetValue(sql);
        }
        public int GetUserDepNum(string userId, string dptId)
        {
            string sql = "select count(*) from st_user_department where userid='" + userId + "' and ORDER_ID='" + dptId + "'";
            return Convert.ToInt32(dataAccess().GetValue(sql));
        }
        public int GetUserGptNum(string userId, string GptId)
        {
            string sql = "select count(*) from st_user_group where userid='" + userId + "' and GID='" + GptId + "'";
            return Convert.ToInt32(dataAccess().GetValue(sql));
        }
        public void DeleteUserDpt(string userId, string dptId)
        {
            string sql = "delete from st_user_department where USERID='" + userId + "' and ORDER_ID='" + dptId + "'";
            dataAccess().RunSql(sql);
        }
        public void DeleteUserGpt(string userId, string gId)
        {
            string sql = "delete from st_user_group where USERID='" + userId + "' and GID='" + gId + "'";
            dataAccess().RunSql(sql);
        }
        public DataTable GetAllUser(string userName, string dwName, string bmName)
        {
            string strSql = string.Empty;
            if (!string.IsNullOrEmpty(dwName) && dwName != "单位")
            {
                strSql = " SELECT distinct m.* FROM st_user m , st_user_department n, st_department k WHERE m.userid = n.userid and n.order_id = k.departid";
                if (!String.IsNullOrEmpty(userName))
                {
                    strSql += " and( m.User_Name LIKE '%" + userName + "%' or m.LOGIN_NAME like '%" + userName + "%')";
                }
                string result0 = string.Empty;
                string strSql0 = string.Format("select count(*)  from st_department h where h.parent_id in (select h.departid  from st_department h where h.depart_name='" + dwName + "'  and h.parent_id='0')");
                result0 = dataAccess().GetValue(strSql0);
                if (result0 == "0")
                {
                    strSql += " and k.depart_name = '" + dwName + "'";
                }
                else
                {
                    if (!string.IsNullOrEmpty(bmName) && bmName != "部门")
                    {
                        strSql += " and k.depart_name like '%" + bmName + "%'   and k.departid in (select j.departid  from st_department j  where j.parent_id in  (select h.departid from st_department h where h.depart_name = '" + dwName + "'))";
                    }
                    else
                    {
                        strSql += "  and k.departid in (select j.departid  from st_department j  where j.parent_id in  (select h.departid from st_department h where h.depart_name = '" + dwName + "'))";
                    }
                }
                strSql += " order by orderbyid asc";
            }
            else
            {
                strSql = @"select m.* from ST_USER m where 1=1";
                if (!String.IsNullOrEmpty(userName))
                {
                    strSql += string.Format(@" and m.User_Name LIKE '%{0}%' or m.LOGIN_NAME like '%{1}%'", userName, userName);
                }
            }
            DataTable dt;
            dataAccess().RunSql(strSql, out dt);
            return dt;
        }
        /// <summary>
        /// 根据userid获取所属部门列表
        /// </summary>
        /// <param name="strUserId"></param>
        /// <returns></returns>
        public DataTable GetSubDepList(string strUserId)
        {
            String strSql = "select * from st_user_department where userid='" + strUserId + "'";
            DataTable dt;
            dataAccess().RunSql(strSql, out dt);
            return dt;
        }
        /// <summary>
        /// 获取上级部门id,如果为0则返回本部门id
        /// </summary>
        /// <param name="strDepId"></param>
        /// <returns></returns>
        public string GetParentDepId(string strDepId)
        {
            string strSql = "select parent_id from st_department where departid='" + strDepId + "'";
            string strResult = dataAccess().GetValue(strSql);
            if (strResult == "0")
            {
                return strDepId;
            }
            return strResult;
        }
        /// <summary>
        /// 根据用户id获取用户信息
        /// </summary>
        /// <returns></returns>
        public DataTable GetUserById(string strUserId)
        {
            string strSql = string.Format(@"select m.*,(Select t.DEPART_NAME From st_user_department n,
                st_department t  Where n.order_id=t.departid And n.userid=m.userid  And Rownum=1) DEPART_NAME,
                (Select n.order_id From st_user_department n Where n.userid=m.userid  And Rownum=1) DEPARTID 
                from st_user m Where m.userId = '{0}'", strUserId);
            DataTable dtOut;
            dataAccess().RunSql(strSql, out dtOut);
            return dtOut;
        }
        /// <summary>
        /// 用户基本信息更新 所在部门不作变更 Operation_flag标志为置为2
        /// </summary>
        /// <returns></returns>
        public void UpdateUserBasicInfo(string userId, string loginName, string userName, string orderbyid, string mobile, string password)
        {
            string strSql = string.Format(@"update ST_USER set LOGIN_NAME='{0}',USER_NAME='{1}',PASSWORD='{2}',MOBILE='{4}',ORDERBYID='{5}' 
where userid='{3}'", loginName, userName, password, userId, mobile, orderbyid);
            dataAccess().RunSql(strSql);
        }
        /// <summary>
        /// 根据id更新用户信息
        /// </summary>
        /// <param name="userInfo">用户信息</param>
        public void UpdateUserById(string userId, string loginName, string userName, string orderbyid, string mobile, string password, string[] UserDepartId)
        {
            IDataAccess ida = dataAccess(true);
            try
            {
                string strSql = string.Format(@"update ST_USER set LOGIN_NAME='{0}',USER_NAME='{1}', PASSWORD='{2}',MOBILE='{4}',
ORDERBYID='{5}' where userid='{3}'", loginName, userName, password, userId, mobile, orderbyid);
                ida.RunSql(strSql);

                string Sql = "delete from st_user_department where userid='" + userId + "'";
                ida.RunSql(Sql);

                foreach (string depart in UserDepartId)
                {
                    if (!string.IsNullOrEmpty(depart))
                    {
                        string ssql = "insert into st_user_department(userid,order_id) values "
                                            + "('" + userId + "'," + "'" + depart + "')";
                        ida.RunSql(ssql);
                    }
                }
                ida.Close(true);
            }
            catch (Exception)
            {
                ida.Close(false);
                throw;
            }

        }
        /// <summary>
        /// 添加用户
        /// </summary>
        /// <param name="user">存储用户信息</param>
        /// <returns></returns>
        public void AddNewUser(string userId, string loginName, string userName, string orderbyid, string mobile, string password, string[] UserDepartId)
        {
            IDataAccess ida = dataAccess(true);
            try
            {
                string strSql = string.Format("insert into ST_USER(USERID,LOGIN_NAME,USER_NAME,PASSWORD,orderbyid,mobile) "
                    + "values('{0}','{1}','{2}','{3}','{4}','{5}')", userId, loginName, userName, password, orderbyid, mobile);
                ida.RunSql(strSql);

                strSql = string.Format("insert into st_session(sid,userid) " +
                    "values('{0}','{1}')", userId, userId);
                ida.RunSql(strSql);

                foreach (string depart in UserDepartId)
                {
                    strSql = "insert into st_user_department(userid,order_id) values "
                        + "('" + userId + "'," + "'" + depart + "')";
                    ida.RunSql(strSql);
                }
                ida.Close(true);
            }
            catch (Exception)
            {
                ida.Close(false);
                throw;
            }

        }
        public string UserDepartmentInfo(string userid)
        {
            DataTable dt = new DataTable();
            string strSql = "select order_id from st_user_department where userid='" + userid + "'";
            dataAccess().RunSql(strSql, out dt);
            string dtStr = string.Empty;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                dtStr += dt.Rows[i][0].ToString() + ",";
            }
            return dtStr;

        }
        public DataTable GetDepartByType(string type, string selComId)
        {
            string sql = string.Empty;
            if (type == "parent")
            {
                sql = "select departid,depart_name from st_department where parent_id='" + selComId + "'";
            }
            else if (type == "depart")
            {
                sql = "select departid,depart_name from st_department where departid ='" + selComId + "' and PARENT_ID not in ('0')";
            }
            DataTable dt = null;
            dataAccess().RunSql(sql, out dt);
            return dt;
        }
        /// <summary>
        /// 获得所有一级部门列表
        /// </summary>
        /// <returns></returns>
        public DataTable GetYJDepartmentList()
        {
            String strSql = "select * from st_department where parent_id=0 order by order_id";
            DataTable dt;
            dataAccess().RunSql(strSql, out dt);
            return dt;
        }
        public DataTable BindUserGroup(string userid)
        {
            string sql = " select a.group_name from st_group a where a.groupid in (select gid from st_user_group where userid='" + userid + "')";
            DataTable dt;
            dataAccess().RunSql(sql, out dt);
            return dt;
        }
        /// <summary>
        /// 获取所有组信息
        /// </summary>
        /// <returns></returns>
        public DataTable GetAllGroupInfo(string groupName)
        {
            string strSql = "select * from st_group ";
            if (!String.IsNullOrEmpty(strSql)) { strSql += " WHERE Group_Name like '%" + groupName + "%'"; }
            DataTable dtPost;
            dataAccess().RunSql(strSql, out dtPost);
            return dtPost;
        }
        /// <summary>
        /// 更新部门信息
        /// </summary>
        /// <param name="gId"></param>
        /// <param name="gName"></param>
        public bool UpdateGroupInfo(string gId, string gName)
        {
            string sql = string.Empty;
            if (string.IsNullOrEmpty(gId))
            {
                sql = "insert into st_group (GROUPID,GROUP_NAME) values ('" + System.Guid.NewGuid().ToString().Replace("-", "").ToUpper() + "','" + gName + "')";
            }
            else
            {
                sql = "update st_group set GROUP_NAME='" + gName + "' where GROUPID ='" + gId + "'";
            }
            return dataAccess().RunSql(sql) > 0 ? true : false;
        }
        /// <summary>
        /// 获取所有用户信息
        /// </summary>
        /// <returns></returns>
        public DataTable GetAllUserTemp(string groupId, string userName)
        {
            string strUserName = userName.Trim();
            string strSql = string.Empty;
            strSql = @"SELECT m.*,(case when (select count(*) from st_user_group where userid = m.userid 
and gid = '" + groupId + "') > 0 then 1 else 0 end) checked FROM ST_USER m ";
            strSql += " WHERE m.UserId in(SELECT UserId FROM ST_USER_GROUP WHERE GId='" + groupId + "')";
            strSql += @" UNION ALL SELECT m.*,(case when (select count(*) from st_user_group where userid = m.userid 
and gid = '" + groupId + "') > 0 then 1 else 0 end) checked FROM ST_USER ";
            strSql += " m WHERE m.UserId NOT IN(SELECT UserId FROM ST_USER_GROUP WHERE GId='" + groupId + "')";
            if (!String.IsNullOrEmpty(strUserName))
            {
                strSql += " and m.User_Name LIKE '%" + strUserName + "%'";
            }
            DataTable dtPost;
            dataAccess().RunSql(strSql, out dtPost);
            return dtPost;
        }
        /// <summary>
        /// 角色菜单
        /// </summary>
        /// <returns></returns>
        public DataTable GetGroupMenu(string type, string strid, string menuid)
        {
            string sql = string.Empty;
            if (type == "0")
            {
                sql = "select t.* from ST_MENU t where parent_id is null Order By parent_id, order_id Asc";
            }
            else if (type == "1")
            {
                sql = "select t.*,(select Count(*) from ST_MENU_IN_GROUP tt Where GROUPID='" + strid
                    + "' And MENUID=t.id) as isexist from ST_MENU t where parent_id='" + menuid
                    + "' Order By parent_id, order_id Asc";
            }
            DataTable dt = null;
            dataAccess().RunSql(sql, out dt);
            return dt;
        }
        public string SetGroupMenu(string strid, string menuid)
        {
            string sql = "select count(*)  from ST_MENU_IN_GROUP where  GROUPID='" + strid + "' and MENUID='" + menuid + "'";
            return dataAccess().GetValue(sql);
        }
        public bool UpdateGroupMenu(string type, string strid, string menuid)
        {
            string sql = string.Empty;
            if (type == "0")
            {
                sql = "insert into ST_MENU_IN_GROUP(GROUPID,MENUID)values('" + strid + "','" + menuid + "')";
            }
            else if (type == "1")
            {
                sql = "delete from ST_MENU_IN_GROUP where GROUPID='" + strid + "' and MENUID='" + menuid + "'";
            }
            return dataAccess().RunSql(sql) > 0 ? true : false;
        }

        //lyl：此方法修改记录：添加一个参数：creatuserid 创建人
        public void updateStUser(string isoa, string userid, string username, string userloginid, string pwd)
        {
            string sql = string.Empty;
            if (isoa == "0")
            {
                sql = "select count(*) from st_user where userid='" + userid + "'";
                int tt = Convert.ToInt32(dataAccess().GetValue(sql));
                if (tt > 0)
                {
                    sql = "delete from st_user where userid='" + userid + "'";
                    dataAccess().RunSql(sql);
                }
            }
            else if (isoa == "1")
            {
                sql = "select count(*) from st_user where userid='" + userid + "'";
                int tt = Convert.ToInt32(dataAccess().GetValue(sql));
                if (tt == 0)
                {
                    sql = string.Format("insert into st_user (USERID,USER_NAME,LOGIN_NAME,PASSWORD) values ('{0}','{1}','{2}','{3}') ", userid, username, userloginid, pwd);
                    //sql = string.Format("insert into st_user (USER_NAME,LOGIN_NAME,PASSWORD,CREATEUSERID) values ('{0}','{1}','{2}','{3}') ", username, userloginid, pwd, createuserid);
                    dataAccess().RunSql(sql);
                }
            }
        }
        public void InsertIDGL(string iid, string ownerid)
        {
            string sql = string.Format("select count(*) from xt_instance_ext where IID='{0}'", iid);
            if (Convert.ToInt32(dataAccess().GetValue(sql)) == 0)
            {
                sql = string.Format("insert into xt_instance_ext (IID,关联ID) values ('{0}','{1}')", iid, ownerid);
                dataAccess().RunSql(sql);
            }
            else
            {
                sql = string.Format("update xt_instance_ext set 关联ID='{0}' where iid='{1}'", ownerid, iid);
                dataAccess().RunSql(sql);
            }
        }
        public void InsertFWZSBUJDS(string iid, string ownerid)
        {
            string zsjdh = string.Empty;
            string strZI = string.Empty;
            string strDI = string.Empty;
            string strHAO = string.Empty;

            if (!string.IsNullOrEmpty(ownerid))
            {
                string osql = @"select 征收许可证号	from zs_prono  where id in (select pid from zs_zzbcjfenhu where id='" + ownerid + "')";
                zsjdh = dataAccess().GetValue(osql);
            }
            //宁11府征字（1年第1号）
            if (zsjdh.Length > 0)
            {
                string tt = (((zsjdh.Replace("宁", "")).Replace("府征字（", "-")).Replace("年第", "-")).Replace("号）", "");
                string[] temp = tt.Split('-');
                if (temp.Length > 0)
                {
                    strZI = temp[0];
                    strDI = temp[1];
                    strHAO = temp[2];
                }
            }
            if (!string.IsNullOrEmpty(strZI) && !string.IsNullOrEmpty(strDI) && !string.IsNullOrEmpty(strHAO))
            {
                string sql = string.Format("select count(*) from ZS_征收补偿决定书 where IID='{0}'", iid);
                if (Convert.ToInt32(dataAccess().GetValue(sql)) == 0)
                {
                    sql = string.Format("insert into ZS_征收补偿决定书 (IID,字,第,号) values ('{0}','{1}')", iid, strZI, strDI, strHAO);
                    dataAccess().RunSql(sql);
                }
                else
                {
                    sql = string.Format("update ZS_征收补偿决定书 set 字='{0}',第='{1}',号='{2}' where iid='{3}'", strZI, strDI, strHAO, iid);
                    dataAccess().RunSql(sql);
                }
            }
        }
        public void Insertformdata(string iid, string ownerid)
        {
            //号，户号，项目，年，月，日，区，字，第，被征收人，日期 | 评估单位，建筑面积，（门牌号=房屋坐落） | 开户银行，安置费
            string sql = @"insert into ZS_征收补偿决定书 (IID,INPUT_INDEX,被征收人,征收人,地址,住所地,区,项目,年,月,日,年2,月2,日2,年3,月3,日3,评估单位,建筑面积,开户银行,安置费)
select '" + iid + @"' As IID,'0' As INPUT_INDEX,
(select 产权人 from zs_zsperson where id=t.zspid) 被征收人,
( select 征收实施单位 from zs_project where id in(Select pid from zs_prono where id =t.pid)) 征收人,
(select 房屋坐落 from zs_zsperson where id=t.zspid) 地址,
(select 房屋坐落 from zs_zsperson where id=t.zspid) 住所地,
(Select 区属 from zs_project where id in(Select pid from zs_prono where id =t.pid)) 区,
(select 项目名称 from zs_project  where id in (select pid from zs_prono where id = t.pid)) 项目,
(select to_char(SLSJ,'yyyy') from ZS_CASE_REGISTER where iid in ( select iid from xt_instance_ext where 关联ID= t.id))  年,
(select to_char(SLSJ,'MM') from ZS_CASE_REGISTER where iid in ( select iid from xt_instance_ext where 关联ID= t.id)) 月,
(select to_char(SLSJ,'dd') from ZS_CASE_REGISTER where iid in ( select iid from xt_instance_ext where 关联ID= t.id)) 日,
(select to_char(CAIJUEXIADADATE,'yyyy') from ZS_CASE_REGISTER where iid in ( select iid from xt_instance_ext where 关联ID= t.id))  年2,
(select to_char(CAIJUEXIADADATE,'MM') from ZS_CASE_REGISTER where iid in ( select iid from xt_instance_ext where 关联ID= t.id)) 月2,
(select to_char(CAIJUEXIADADATE,'dd') from ZS_CASE_REGISTER where iid in ( select iid from xt_instance_ext where 关联ID= t.id)) 日2,
(select to_char(CUIGAOFACHUDATE,'yyyy') from ZS_CASE_REGISTER where iid in ( select iid from xt_instance_ext where 关联ID= t.id))  年3,
(select to_char(CUIGAOFACHUDATE,'MM') from ZS_CASE_REGISTER where iid in ( select iid from xt_instance_ext where 关联ID= t.id)) 月3,
(select to_char(CUIGAOFACHUDATE,'dd') from ZS_CASE_REGISTER where iid in ( select iid from xt_instance_ext where 关联ID= t.id)) 日3,
(select 征收评估机构 from zs_prono where id = t.pid) 评估单位,
(select 房屋建筑面积 from zs_zsperson where id=t.zspid) 建筑面积,
(select 开户银行 from ZS_BCBASICINFO  where pnid in(select id from zs_project where id in(select pid from zs_prono where id = t.pid))) 开户银行,
(select TEXTBOX25 from zs_hbhxy where personid In (select id from zs_zsperson where id=t.zspid)) 安置费 
from zs_zzbcjfenhu t where id='" + ownerid + "'";
            dataAccess().RunSql(sql);
        }
        public DataSet GetOneDs(string table, string colstr, string querystr)
        {
            string sql = @"select " + colstr + " from " + table + " where 1=1 " + querystr + "";
            DataSet dt = new DataSet();
            dataAccess().RunSql(sql, out dt);
            return dt;
        }
        public void UpdateQMContent(byte[] FileContent, string strId, string userid)
        {
            //MAGICNUM   USERID   SIGNPIC
            string sql = "select count(*) from st_user_sign where USERID='" + userid + "'";
            if (Convert.ToInt32(dataAccess().GetValue(sql)) == 0)
            {
                sql = @"insert into st_user_sign (MAGICNUM,USERID)values('" + strId + "','" + userid + "')";
                dataAccess().RunSql(sql);
            }
            try
            {
                OracleConnection oc = new OracleConnection();
                oc.ConnectionString = base.ProviderProperties["FlowConnectionString"].ToString();
                OracleCommand cmd = new OracleCommand("update st_user_sign set SIGNPIC=:bSIGNPIC  where USERID='" + userid + "'", oc);
                OracleParameter op1 = new OracleParameter("bSIGNPIC", OracleType.LongRaw, FileContent.Length);
                op1.Value = FileContent;
                cmd.Parameters.Add(op1);
                oc.Open();
                cmd.ExecuteNonQuery();
                oc.Close();
            }
            catch
            {

            }
        }
        public string GetQMId(string userid)
        {
            string sql = "select MAGICNUM from st_user_sign where USERID='" + userid + "'";
            return dataAccess().GetValue(sql);
        }
        public DataTable GetSignDT(string strId)
        {
            string sql = "select * from st_user_sign where MAGICNUM='" + strId + "'";
            DataTable dt = new DataTable();
            dataAccess().RunSql(sql, out dt);
            return dt;
        }

        public DataTable GetAllFile(string iid)
        {
            DataTable dt = new DataTable();
            string sql = @"select t.AID,t.NAME,t.ext_name from st_attachment t where aid in (
select res_value from st_dynamic_resource where iid='" + iid + "')";
            dataAccess().RunSql(sql, out dt);
            return dt;
        }
        public bool DelFileByID(string strId)
        {
            bool isdel = false;
            IDataAccess ida = dataAccess(true);
            try
            {
                string sql = "delete from st_attachment where aid='" + strId + "'";
                string sql1 = "delete from ST_DYNAMIC_RESOURCE where RES_VALUE='" + strId + "'";
                if (ida.RunSql(sql1) > 0) { isdel = true; } else { isdel = false; }
                if (ida.RunSql(sql) > 0) { isdel = true; } else { isdel = false; }
                ida.Close(true);
            }
            catch (Exception)
            {
                ida.Close(false);
                throw;
            }

            return isdel;
        }
        public DataTable GetFlowNameByUser(string userId)
        {
            DataTable dt = new DataTable();
            string sql = "select distinct wname from st_workflow where wid in(select wid from st_instance)";

            string strSql = "Select a.userid  From st_user_group a,st_group b Where a.gid=b.groupid "
                + " And (b.group_name='管理员' or b.group_name='数据维护组') And a.userid='" + userId + "'";
            if (String.IsNullOrEmpty(dataAccess().GetValue(strSql)))
            //不是管理员及数据维护组，只能查自己的业务,除非设置过授权
            {
                sql += " And (wid  In(Select wid From st_group_in_step a,st_group b Where a.gid=b.groupid And  "
                    + "gid In(Select gid From st_user_group Where userid='" + userId + "') )"
                    + " OR wname in(SELECT TASK FROM XT_QUERY_RIGHT WHERE USERID='" + userId + "'))";
            }
            dataAccess().RunSql(sql, out dt);
            return dt;
        }
        #endregion

        /// <summary>
        /// type为0为普通方式，1为ftp方式
        /// </summary>
        /// <param name="type"></param>
        /// <param name="aid"></param>
        /// <returns></returns>
        public byte[] getAttByAID(string type, string aid)
        {
            string sql = string.Empty;
            try
            {
                sql = "select ('ftp://' || s.server || ':' || s.port || '/') url, (s.user_name || ':' || s.password || '@' || s.server || ':' || s.port || '@') ftp, s.* from st_server s  where s.identify = '南京国土局机关FTP服务器' and rownum <= 1";
                System.Data.DataTable FtpDT = new System.Data.DataTable();
                IDataAccess ida = dataAccess(false);
                ida.RunSql(sql, out FtpDT);
                if (FtpDT.Rows.Count != 1)
                {
                    throw new Exception("获取FTP基本信息出错");
                }
                sql = string.Format("select * from st_attachment  where aid='{0}'", aid);
                System.Data.DataTable attDT = new System.Data.DataTable();
                ida.RunSql(sql, out attDT);
                //System.Data.DataTable attDT = Antu.BaseClass.DataAccessUtil.GetDT(sql, Antu.BaseClass.DataAccessUtil.Neto2OAConnString);
                if (attDT.Rows.Count != 1)
                {
                    throw new Exception("获取附件信息出错");
                }
                if (type == "0")
                {
                    return (byte[])(attDT.Rows[0]["DATA"]);
                }
                FtpManager _ftpManager = new FtpManager(new Uri(FtpDT.Rows[0]["url"].ToString()), FtpDT.Rows[0]["user_name"].ToString(), FtpDT.Rows[0]["password"].ToString());
                string ftpUrl, ftpPath;
                ftpUrl = attDT.Rows[0]["ftp_path"].ToString();
                string[] list = ftpUrl.Split('@');
                ftpPath = list[2];
                return _ftpManager.DownloadFile(ftpPath.Replace("\\", "/"));
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        public bool IsCJAdmin(string userid)
        {
            string sql = @"select count(*) from st_user_group where gid=(select groupid from st_group where group_name ='超级管理员') and userid='" + userid + "'";
            int isadmin = Convert.ToInt32(dataAccess().GetValue(sql));
            if (isadmin > 0)
            {
                return true;
            }
            return false;
        }
        public string GetSomeStr(string table, string col, string strwhere)
        {
            string sql = "select  " + col + " from " + table + " where 1=1 " + strwhere + "";
            return dataAccess().GetValue(sql);
        }
        public void RunSqlNon(string sql)
        {
            dataAccess().RunSql(sql);
        }

        public DataTable DoDataTableSql(string sql)
        {
            DataTable ds = new DataTable();
            dataAccess().RunSql(sql, out ds);
            return ds;
        }

        #region  根据单位名称加载相应的部门信息
        public DataTable GetDepartmentByDanWei(string danwei)
        {
            DataTable dt = new DataTable();
            string sql = "select DEPART_NAME from ST_DEPARTMENT where PARENT_ID in (select DEPARTID from ST_DEPARTMENT where DEPART_NAME='" + danwei + "') ";
            dataAccess().RunSql(sql, out dt);
            return dt;
        }
        #endregion
        public string DeleteFlows(string iid)
        {
            string temp = string.Empty;
            IDataAccess ida = dataAccess(true);
            try
            {
                string sql = @"select res_value from st_dynamic_resource where type=2 and iid='" + iid + "'";
                DataTable dt = new DataTable();
                dataAccess().RunSql(sql, out dt);
                if (dt.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        ida.RunSql("delete from st_attachment where aid='" + dt.Rows[i]["res_value"].ToString() + "'");
                    }
                }
                ida.RunSql("delete from st_dynamic_resource where iid='" + iid + "'");
                DataTable dt1 = new DataTable();
                dataAccess().RunSql(@"Select Distinct db_table From st_form_input Where fid In (
Select RES_VALUE From st_static_resource Where wid In (
Select wid From st_instance Where iid='" + iid + "'))", out dt1);
                if (dt1.Rows.Count > 0)
                {
                    for (int i = 0; i < dt1.Rows.Count; i++)
                    {
                        string formTableName = dt1.Rows[i]["DB_TABLE"].ToString();
                        if (!string.IsNullOrEmpty(formTableName))
                        {
                            if (Convert.ToInt32(dataAccess().GetValue("select count(*) from " + formTableName + " where iid='" + iid + "'")) > 0)
                            {
                                ida.RunSql("delete from " + formTableName + " where iid='" + iid + "'");
                            }
                        }
                    }
                }
                ida.RunSql("delete from st_work_item where iid='" + iid + "'");
                ida.RunSql("delete from st_suspend where iid='" + iid + "'");
                ida.RunSql("delete from st_work_item_hist where iid='" + iid + "'");
                ida.RunSql("delete from xt_instance_ext where iid='" + iid + "'");
                ida.RunSql("delete from st_instance where iid='" + iid + "'");
                ida.Close(true);
            }
            catch (Exception ex)
            {
                ida.Close(false);
                temp = "删除失败，原因是" + ex.Message;
            }
            return temp;
        }
        #region 流程附件相关代码
        public void InsertFiles(string strAId, string fname, string fileext, string strPath, string strIId, string strWIId, string strUserId)
        {
            string gpath = string.Empty;
            if (!string.IsNullOrEmpty(strPath))
            {
                string sql0 = "select path from st_static_resource where id='" + strPath + "'";
                gpath = dataAccess().GetValue(sql0);
            }

            string sql = "select path from st_dynamic_resource t where iid='" + strIId + "' And path Like '" + fname + "%'";
            if (!string.IsNullOrEmpty(gpath))
            {
                sql = "select path from st_dynamic_resource t where iid='" + strIId + "' And path Like '" + gpath + "/" + fname + "%'";
            }
            DataTable dt = new DataTable();
            dataAccess().RunSql(sql, out dt);
            string temp = string.Empty;
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    string tt1 = dt.Rows[i]["path"].ToString();
                    if (tt1.IndexOf("/") > -1)
                    {
                        string[] t1 = tt1.Split('/');
                        string t2 = t1[t1.Length - 1].ToString();
                        string tt2 = t2.Replace(fname, "");
                        if (tt2.Length > 0)
                        {
                            string tt3 = tt2.Replace("-", "");
                            if (tt3.Length > 0)
                            {
                                int tt4 = Convert.ToInt32(tt3) + 1;
                                temp = fname + "-" + tt4.ToString();
                            }
                        }
                        else
                        {
                            if ((gpath + "/" + fname) == tt1)
                            {
                                temp = fname + "-1";
                            }
                        }
                    }
                    else
                    {
                        string tt7 = tt1.Replace(fname, "");
                        if (tt7.Length > 0)
                        {
                            string tt8 = tt7.Replace("-", "");
                            if (tt8.Length > 0)
                            {
                                int tt9 = Convert.ToInt32(tt8) + 1;
                                temp = fname + "-" + tt9.ToString();
                            }
                        }
                        else
                        {
                            if (fname == tt1)
                            {
                                temp = fname + "-1";
                            }
                        }
                    }

                }
            }
            if (!string.IsNullOrEmpty(temp))
            {
                fname = temp;
            }
            string tpath = string.Empty;
            if (strPath.Equals("/") || string.IsNullOrEmpty(strPath))
            {
                //tpath = "（" + fileext.Substring(1, fileext.Length - 1) + "）" + fname;
            }
            else
            {
                tpath = gpath + "/";
            }
            IDataAccess ida = dataAccess(true);
            string sql1 = "insert into st_attachment (AID,MAGIC_NUMBER,Name,EXT_NAME) values ('" + strAId + "','1','" + fname + "','" + fileext + "')";
            string sql2 = "insert into st_dynamic_resource (IID,PATH,SNAME,USERID,TYPE,RES_VALUE) values ('" + strIId + "','" + tpath + fname + "',(Select step From st_work_item Where wiid='" + strWIId + "' and rownum =1 ),'" + strUserId + "', '2','" + strAId + "')";
            try
            {
                ida.RunSql(sql1);
                ida.RunSql(sql2);
                ida.Close(true);
            }
            catch (Exception ex)
            {
                ida.Close(false);
            }
        }
        public void UpdateFileData(string strAId, byte[] bFileContent)
        {
            System.Data.OracleClient.OracleConnection oc = new System.Data.OracleClient.OracleConnection();

            oc.ConnectionString = System.Configuration.ConfigurationManager.AppSettings["OracleConnectionString"].ToString();
            System.Data.OracleClient.OracleCommand cmd = new System.Data.OracleClient.OracleCommand("update st_attachment set DATA=:bFileContent where aid='" + strAId + "'", oc);
            //System.Data.OracleClient.OracleCommand cmd = new System.Data.OracleClient.OracleCommand("insert into st_attachment (AID,MAGIC_NUMBER,Name,EXT_NAME,DATA) values('" + strAId + "','1','" + fname + "','" + fileext + "',:bFileContent)", oc);
            System.Data.OracleClient.OracleParameter op1 = new System.Data.OracleClient.OracleParameter("bFileContent", System.Data.OracleClient.OracleType.Blob, bFileContent.Length);
            op1.Value = bFileContent;
            cmd.Parameters.Add(op1);
            oc.Open();
            cmd.ExecuteNonQuery();
            oc.Close();
        }
        #endregion

        #region 归档时置状态位
        /// <summary>
        /// 归档时置状态位
        /// </summary>
        /// <param name="iid"></param>
        /// <param name="wid"></param>
        /// <param name="wiid"></param>
        public void FlowToGuiDang(string iid, string wid, string wiid)
        {
            //获得下一个提交岗位，如果没有下一个岗位，就置状态位           
            string sql1 = "select wname from st_workflow where wid='" + wid + "'";
            string sql2 = @"select fname from st_flow where sname In (Select STEPCTLID From ST_WORK_ITEM Where WIID='" + wiid + @"')
 and wid In (Select wid From st_instance Where iid ='" + iid + "')";
            string wname = dataAccess().GetValue(sql1);
            string nextStep = dataAccess().GetValue(sql2);
            string sql = "select 关联ID from xt_instance_ext where iid='" + iid + "'"; ;
            //查找关联ID
            string pid = dataAccess().GetValue(sql);
            IDataAccess ida = dataAccess(true);
            try
            {
                //                string typesql = @"Select t.sname From st_step t,st_flow t1 
                //Where t.ctlid=t1.sname 
                //And t.wid='"+wid+@"' 
                //And t1.wid='"+wid+@"' 
                //And t.ctlid=(Select STEPCTLID From ST_WORK_ITEM Where WIID='"+wiid+@"')
                //And t.step_type=2";
                //                string  sname = dataAccess().GetValue(typesql);
                //               //当案件为“未办结”
                //                if (!string.IsNullOrEmpty(sname))
                //                {
                if (wname == "房屋征收程序申请" || wname == "房屋征收立项申请")
                {
                    if (IsLast(nextStep))
                    {
                        sql = "update zs_project set 审批状态=1,can_sync=1 where id='" + pid + "'";
                        ida.RunSql(sql);
                        SetFileCanSync(pid, 1);
                    }
                }
                else if (wname == "房屋征收冻结申请")
                {
                    if (IsLast(nextStep))
                    {
                        sql = "update zs_project set 冻结审批状态=1,can_sync=1 where id='" + pid + "'";
                        ida.RunSql(sql);
                    }
                }
                else if (wname == "房屋征收补偿实施方案申请")
                {
                    if (IsLast(nextStep))
                    {
                        sql = "update zs_project set 补偿实施审批状态=1,can_sync=1 where id='" + pid + "'";
                        ida.RunSql(sql);
                        SetFileCanSync(pid, 7);
                    }
                }
                else if (wname == "房屋征收决定申请")
                {
                    if (IsLast(nextStep))
                    {
                        sql = "update zs_prono set 审批状态=1 where id='" + pid + "'";
                        ida.RunSql(sql);
                        string ssql = "update zs_project set can_sync=1 where id in(select pid from zs_prono where id='" + pid + "')";
                        ida.RunSql(ssql);
                        SetFileCanSync(pid, 9);
                    }
                }
                else if (wname == "房屋征收决定申请(区县)")
                {
                    if (IsLast(nextStep))
                    {
                        sql = "update zs_prono set 审批状态=1 where id='" + pid + "'";
                        ida.RunSql(sql);
                        string ssql = "update zs_project set can_sync=1 where id in(select pid from zs_prono where id='" + pid + "')";
                        ida.RunSql(ssql);
                        SetFileCanSync(pid, 9);
                    }
                }
                else if (wname == "房屋征收补偿资金监管")
                {
                    if (IsLast(nextStep))
                    {
                        sql = "update ZS_BCBASICINFO set 开户审核状态=3 where id='" + pid + "'";
                        ida.RunSql(sql);
                        string ssql = "update zs_project set can_sync=1 where id in(select pnid from ZS_BCBASICINFO where id='" + pid + "')";
                        ida.RunSql(ssql);
                    }
                }
                else if (wname == "房屋征收补偿决定审批(预审)")
                {
                    if (IsLast(nextStep))
                    {
                        sql = "update ZS_BCJD set 审批状态=1 where pnid=(select pid from  zs_zzbcjfenhu where zspid='" + pid + "') and personid='" + pid + "'";
                        ida.RunSql(sql);
                        string ssql = "update zs_project set can_sync=1 where id in(select pid from zs_prono where id in( select pid from  zs_zzbcjfenhu where zspid='" + pid + "'))";
                        ida.RunSql(ssql);
                    }
                }
                else if (wname == "房屋征收补偿决定审批(区县预审)")
                {
                    if (IsLast(nextStep))
                    {
                        sql = "update ZS_BCJD set 审批状态=1 where pnid=(select pid from  zs_zzbcjfenhu where zspid='" + pid + "') and personid='" + pid + "'";
                        ida.RunSql(sql);
                        string ssql = "update zs_project set can_sync=1 where id in(select pid from zs_prono where id in( select pid from  zs_zzbcjfenhu where zspid='" + pid + "'))";
                        ida.RunSql(ssql);
                    }
                }
                else if (wname == "房屋征收补偿方案申请")
                {
                    if (IsLast(nextStep))
                    {
                        sql = "update zs_project set 补偿审批状态=1,can_sync=1 where id='" + pid + "'";
                        ida.RunSql(sql);
                        SetFileCanSync(pid, 6);
                    }
                }
                // }

                ida.Close(true);
            }
            catch (Exception ex)
            {
                ida.Close(false);
            }

        }

        /// <summary>
        /// 判断是否是最后一个岗位
        /// </summary>
        /// <param name="nextStep"></param>
        /// <returns></returns>
        public bool IsLast(string nextStep)
        {
            bool flag = false;
            string[] lastStep = System.Configuration.ConfigurationManager.AppSettings["lastStep"].ToString().Split(',');
            foreach (string item in lastStep)
            {
                if (nextStep == item)
                {
                    flag = true;
                    break;
                }
            }
            return flag;
        }

        /// <summary>
        /// 附件状态为的交换
        /// </summary>
        /// <param name="txt"></param>
        /// <returns></returns>
        public void SetFileCanSync(string itemid, int use_type)
        {
            string sql = "update zs_file set can_sync=1 where itemid='" + itemid + "' and use_type=" + use_type;
            dataAccess().RunSql(sql);
        }
        #endregion

        #region  返回模糊查询中的案件号
        public string GetCaseIdLike(string txt)
        {
            string str = string.Empty;
            if (txt.Trim().Length == 8)
            {
                int i;
                if (int.TryParse(txt.Trim(), out i))
                {
                    string nian = txt.Trim().Substring(0, 4);
                    string yue = txt.Trim().Substring(4, 4);
                    str = nian + "）第" + yue;
                }
                else
                {
                    str = txt;
                }
            }
            else if (txt.Trim().Length > 4 && txt.Trim().Length < 8)
            {
                int i;
                if (int.TryParse(txt.Trim(), out i))
                {
                    string nian = txt.Trim().Substring(0, 4);
                    string yue = txt.Trim().Substring(4, (txt.Trim().Length - 4));
                    str = nian + "）第" + yue;
                }
                else
                {
                    str = txt;
                }
            }
            else
            {
                str = txt;
            }
            return str;
        }
        #endregion

        /// <summary>
        /// 根据条件返回字符串
        /// </summary>
        /// <param name="table"></param>
        /// <param name="column"></param>
        /// <param name="where"></param>
        /// <returns></returns>
        public string GetStringBy(string table, string column, string where)
        {
            string str = string.Empty;
            if (!string.IsNullOrEmpty(table) && !string.IsNullOrEmpty(column))
            {
                string sql = "select " + column + " from " + table + " where 1=1" + where;
                str = dataAccess().GetValue(sql);
            }
            return str;
        }


        #region 新挂起,解挂
        /// <summary>
        /// 申请挂起流程
        /// </summary>
        /// <param name="iid"></param>
        /// <param name="wiid"></param>
        /// <param name="userid"></param>
        /// <param name="memo"></param>
        /// <returns></returns>
        public bool NewApplySuspend(string iid, string wiid, string userid, string memo)
        {
            bool result = false;
            try
            {
                string sql = string.Format("select count(*) from st_work_item where iid='{0}' and wiid='{1}' and active>=0", iid, wiid);
                if (dataAccess().GetValue(sql) == "1")
                {
                    sql = string.Format("select id from XT_SUSPEND_APPLY where SERIAL='{0}' and HANDLEID='{1}'", iid, wiid);
                    string id = dataAccess().GetValue(sql);
                    if (string.IsNullOrEmpty(id))
                    {
                        sql = string.Format("insert into XT_SUSPEND_APPLY(id,SERIAL,HANDLEID,USERID,APPLY_TIME,MEMO)  values(SS_ST_SUSPEND_ID.Nextval,'{0}','{1}','{2}',sysdate,'{3}')", iid, wiid, userid, memo);

                    }
                    else
                    {
                        sql = @"update xt_suspend_apply set ISDELETE=null,USERID='" + userid + "',APPLY_TIME=sysdate,MEMO='" + memo + "' where id='" + id + "'";

                    }
                    string ssql = "select id from st_suspend where iid='" + iid + "'";
                    string sid = dataAccess().GetValue(ssql);
                    if (!string.IsNullOrEmpty(sid))
                    {
                        ssql = "delete from st_suspend where id='" + sid + "'";
                    }
                    dataAccess().RunSql(sql);
                    dataAccess().RunSql(ssql);
                    result = true;
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                dataAccess().Close();
            }
            return result;
        }

        /// <summary>
        /// 确认挂起流程（加事务）
        /// </summary>
        /// <returns></returns>
        public bool NewSuspendWorkFlow(string iid, string wiid, string userid, string description)
        {
            IDataAccess ida = dataAccess(true);
            bool result = false;
            try
            {
                string sql = string.Format("select count(*) from st_work_item  where iid='{0}' and wiid='{1}' and active>=0", iid, wiid);
                if (dataAccess().GetValue(sql) == "1")
                {
                    sql = string.Format("insert into st_suspend  (iid, start_date, userid, description, apply_id) values  ('{0}', sysdate, '{1}', '{2}', '{3}')", iid, userid, description, wiid);
                    string ssql = string.Format("update XT_SUSPEND_APPLY set CHECKUSERID = '{0}', CHECK_TIME = sysdate, ISCHECKUP = 1  where serial = '{1}' and HANDLEID = '{2}' and ISCHECKUP is null", userid, iid, wiid);
                    dataAccess().RunSql(sql);
                    dataAccess().RunSql(ssql);
                    ida.Close(true);
                    result = true;
                }
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                ida.Close(false);
            }
            return result;
        }

        /// <summary>
        /// 解挂
        /// </summary>
        /// <param name="iid"></param>
        /// <param name="userid"></param>
        /// <returns></returns>
        public string NewDelApply(string iid, string userid)
        {
            IDataAccess ida = dataAccess(true);
            string temp = string.Empty;
            try
            {
                string sql = string.Format("update st_suspend set end_date = sysdate,end_userid = '{0}' where iid = '{1}' and end_date is null", userid, iid);

                string ssql = string.Format("update xt_suspend_apply set isdelete='1' ,delete_time=sysdate where serial='{0}' and isdelete is null", iid);
                dataAccess().RunSql(sql);
                dataAccess().RunSql(ssql);
                ida.Close(true);
            }
            catch (Exception ex)
            {
                ida.Close(false);
                temp = "删除失败，原因是" + ex.Message;
            }
            return temp;
        }

        /// <summary>
        /// 确认人获取已确认挂起列表
        /// </summary>
        /// <returns></returns>
        public DataTable NewGetSuspendListByUser(string iid, string name, string wname, string userid, string type)
        {

            DataTable dt;
            try
            {
                string sql = "select s.id,s.iid, w.wname, i.name, s.description, s.start_date, s.end_date  from st_workflow w, st_instance i, st_suspend s  where w.wid = i.wid and i.iid = s.iid";
                sql += iid == "" ? "" : " and i.iid like'%" + iid + "%'";
                sql += name == "" ? "" : " and i.name like'%" + name + "%'";
                sql += wname == "" ? "" : " and w.wname like'%" + wname + "%'";
                sql += userid == "" ? "" : " and s.userid='" + userid + "'";
                if (!string.IsNullOrEmpty(type))
                {
                    if (type == "yjg")
                    {
                        sql += " and end_date is not null ";
                    }
                    if (type == "wjg")
                    {
                        sql += " and end_date is null ";
                    }
                }
                sql += " order by  s.start_date desc";
                dataAccess().RunSql(sql, out dt);
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                dataAccess().Close();
            }
            return dt;
        }

        /// <summary>
        /// 取消申请挂起
        /// </summary>
        public bool NewCancelApplySuspend(string iid, string userid)
        {
            bool result = false;

            try
            {
                string sql = string.Format("update xt_suspend_apply set isdelete='1' ,checkuserid='{0}',delete_time=sysdate where serial='{1}' and delete_time is null", userid, iid);
                dataAccess().RunSql(sql);
                result = true;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                dataAccess().Close();
            }
            return result;
        }
        #endregion

        /// <summary>
        /// 根据id更新指定业务的优先级别
        /// </summary>
        /// <param name="strSerialId">业务id</param>
        /// <param name="strSerialStatus">业务休闲级</param>
        /// <returns></returns>
        public int UpdateInstancePriority(string strSerialId, string strSerialStatus)
        {
            string strSql = string.Format("update st_instance set priority='{0}'" +
                " where iid='{1}'", strSerialStatus, strSerialId);
            return dataAccess().RunSql(strSql);
        }
        public string GetStepByWiid(string wiid)
        {
            string strSql = "select step from st_work_item where wiid=" + wiid;
            return dataAccess().GetValue(strSql);
        }
        public string GetStr(string strSql)
        {
            return dataAccess().GetValue(strSql);
        }
        public DataTable GetDT(string strSql)
        {
            DataTable dt = new DataTable();
            dataAccess().RunSql(strSql, out dt);
            return dt;
        }
        public int RunStrSql(string strSql)
        {
            return dataAccess().RunSql(strSql);
        }

        #region 保障中的案件受理所用到的方法
        /// <summary>
        /// 经济适用房申请
        /// </summary>
        /// <param name="iid"></param>
        /// <param name="ownerid"></param>
        public void InsertLuoYangJSFSQ(string iid, string ownerid)
        {
            IDataAccess ida = dataAccess(true);
            try
            {
                string sql = @"insert into UT_洛阳经济适用房申请审批表(IID,申请人姓名,家庭人口,小区名称,小区名称及房号,座落位置及面积,建筑面积,申请人签名,联系电话,日期1,申请人配偶,ID) select
    '" + iid + @"' IID,
    Name 申请人姓名,
    (select count(*) from at_family where sid='" + ownerid + @"') 家庭人口,
	xqmc 小区名称,
    xqmc||' 房号:'||fjh 小区名称及房号, 
    ADDRESS||' 面积:'||fwmj 座落位置及面积,
	fwmj 建筑面积,
    name 申请人签名,
    lxfs 联系电话,
    ApplyTime 日期1,
    (select 姓名 from at_family where sid='" + ownerid + @"' and 关系='配偶') 申请人配偶,
	'" + ownerid + @"' ID
from at_jsfbmryxx where id='" + ownerid + "'";
                string tsql = @"update at_family set iid='" + iid + "',TABLENAME='UT_洛阳经济适用房申请审批表' where sid ='" + ownerid + "'";
                string ssql = @"update at_jsfbmryxx set BMState='0' where id='" + ownerid + "'";
                ida.RunSql(sql);
                ida.RunSql(tsql);
                ida.RunSql(ssql);
                ida.Close(true);
            }
            catch (Exception)
            {
                ida.Close(false);
                throw;
            }
        }

        /// <summary>
        /// 自管公房出售审批
        /// </summary>
        /// <param name="iid"></param>
        /// <param name="ownerid"></param>
        public void InsertLuoYangGYFSP(string iid, string ownerid)
        {
            //房屋座落位置,楼号,房号,套型,竣工时间,使用年限,结构等级,建筑面积,折减面积,计价建筑面积
            string sql = string.Empty;
            sql = @"insert into UT_职工购买公房登记审批表(IID,房屋座落位置,楼号,房号,套型,竣工时间,使用年限,结构等级,建筑面积,折减面积,计价建筑面积)
							select	   '" + iid + @"' IID,
										   t.地址 房屋座落位置,
										   t.所属幢号 楼号,
										   t.户室号 房号,
										   t.房屋户型 套型,
										   t.竣工时间 竣工时间,
										   t.使用年限 使用年限,
										   t.结构等级 结构等级,
										   t.面积 建筑面积,
										   t.折减面积 折减面积,
										   t.计价建筑面积 计价建筑面积
									from (select f.*,z.地址 from at_gyzf_fangwu f,at_gyzf_zhuang z where f.zid=z.id) t
								where t.id='" + ownerid + "'";
            dataAccess().RunSql(sql);
        }

        /// <summary>
        /// 插入数据到ut_保障性住房退出审批表
        /// </summary>
        /// <param name="iid"></param>
        /// <param name="ownerid">at_loupanuser中的唯一ID</param>
        public void InsertLuoYangBZFTCSP(string iid, string ownerid)
        {
            string sql = string.Empty;
            sql = @"insert into ut_保障性住房退出审批表
  (IID,
   被保障人姓名,
   性别,
   联系电话,
   身份证号码,
   被保障人数,
   申请日期,
   申请号,
   所属区,
   保障性住房房源地址,
   住房面积,
   丘权号)
 select 
      '" + iid + @"' IID,
      g.姓名 被保障人姓名,
      (select 性别 from at_family where id = g.familyid) 性别,
      (select 联系电话 from at_family where id = g.familyid) 联系电话,
      (select 证件号码 from at_family where id = g.familyid) 身份证号码,
      (select count (id)+1 from at_family where hzid = g.familyid) 被保障人数,
      g.addtime 申请日期,
      g.申请表编号 申请号,
      (select 区属 from at_prolist where id = g.proid) 所属区,
      (select 地址 from at_zhuangbasic where id = g.zid) 保障性住房房源地址,
      (select 预测建筑面积 from at_loupan where id = g.fid) 住房面积,
      (select 丘权号 from at_zhuangbasic where id = g.zid) 丘权号
  from at_loupanuser g where id ='" + ownerid + "'";
            dataAccess().RunSql(sql);
        }

        /// <summary>
        /// 单位申请公租房
        /// </summary>
        /// <param name="iid"></param>
        /// <param name="Gzpcid"></param>
        public bool InsertDWSQGZF(string iid, string Gzpcid, string userid)
        {
            bool result = true;
            IDataAccess ida = dataAccess(true);
            try
            {
                string strSql = "";
                string xsql = "update XT_INSTANCE_EXT set GZPCID='" + Gzpcid + "' where iid='" + iid + "' ";
                ida.RunSql(xsql);

                strSql = @"select t.* from Gz_Pici_Pic t,gz_pici t1 where t.gzpcid=t1.id and t.gzpcid = '" + Gzpcid + "'";
                DataTable dt = new DataTable();
                dataAccess().RunSql(strSql, out dt);
                //附件的插入
                if (dt.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {

                        string fid = Guid.NewGuid().ToString().Replace("-", "");

                        string name = dt.Rows[i]["Name"].ToString();
                        string filename = string.Empty;
                        string ext = string.Empty;
                        if (!string.IsNullOrEmpty(name))
                        {
                            filename = name.Split('.')[0];
                            ext = "." + name.Split('.')[1];
                        }
                        string asql = @"insert into st_attachment 
select '" + fid + "',1,'" + ext + "',pic,'" + filename + "',null from Gz_Pici_Pic where id = '" + dt.Rows[i]["id"] + "'";
                        ida.RunSql(asql);
                        string dsql = @"insert into st_dynamic_resource(IID,	PATH,
                SNAME,	
                USERID,	
                TYPE,	
                RES_VALUE,	
                INPUT_INDEX,	
                VERSION,	
                ADDTIME,	
                RTID,	
                ORDERNO) 
                values('" + iid + "','附件/'||'批次扫描件" + i + "','受理','" + userid + "',2,'" + fid + "',null,1,null,null,0)";
                        ida.RunSql(dsql);
                    }
                }

                string usql = @"insert into UT_单位申请政府公租房审批表
  (iid,档号,编号,单位名称,单位性质,法定代表人, 注册地点, 法人代码, 主要经营内容, 一般预算收入纳税额度, 单位地址, 联系人, 联系电话, 单位劳动合同制员工人数, 拟申请公共租赁住房人数, 拟申请公共租赁住房套数, 法定代表人1,承诺书日期)
  select '" + iid + @"','GZ02-'||(select loginid from gz_user tt where tt.id = t.userid)||'-'||To_char(sysdate,'yyyy') 档号,(select loginid from gz_user tt where tt.id=t.userid) loginid, 企业名,单位性质,法定代表人, 注册地点, 法人代码, 主要经营内容, 一般预算收入纳税额度, 单位地址, 联系人, 联系电话, 单位劳动合同制员工人数, 拟申请公共租赁住房人数, 拟申请公共住房套数, 法人代码,sysdate  from gz_company t   where userid in (select userid from gz_pici where ID='" + Gzpcid + "')";
                ida.RunSql(usql);

                ida.Close(true);
            }
            catch (Exception)
            {
                ida.Close(false);
                result = false;
                throw;
            }
            return result;

        }



        /// <summary>
        /// 添加户主信息和家人
        /// </summary>
        /// <param name="dr"></param>
        public void InsertFamily(DataRow dr)
        {
            IDataAccess ida = dataAccess(true);
            try
            {
                string aid = System.Guid.NewGuid().ToString().Replace("-", "").ToUpper();
                string asql = @"insert into at_family 
(ID, 姓名, 证件类型, 证件号码, 联系电话, 
住址, 关系, FID, HZID, TABLENAME, 
SID, 申请类型, IID, CID, 工龄, 住房补贴, 性别, 
单位, 住房情况, 婚否, 月收入, 备注, 职务, 通知书编号)
values ('" + aid + @"', '" + dr["姓名"] + @"', '身份证', '" + dr["身份证号"] + @"', 
'" + dr["联系电话"] + @"', '', '1', '', '', 
'" + dr["tablenamenew"] + @"', '" + dr["ID"] + @"', 
'" + dr["WNAME"] + @"', '" + dr["IID"] + @"', '', '', '', 
'" + dr["性别"] + @"', '" + dr["工作单位"] + @"', '', '" + dr["婚姻状况"] + @"', '', 
'', '', '')";
                ida.RunSql(asql);

                string sql = @"select * from at_family where 姓名='" + dr["姓名"] + @"'
            and 证件号码 ='" + dr["身份证号"] + "' and TABLENAME='" + dr["tablenamenew"] + "'" + @"
            and hzid is null and iid ='" + dr["IID"] + "' and sid = '" + dr["ID"] + "' ";
                DataTable dt = new DataTable();
                ida.RunSql(sql, out dt);
                if (dt.Rows.Count > 0)//如果该户主存在的
                {
                    sql = @" select * from GZ_COMPERSON where personid = '" + dr["ID"] + "'";
                    DataTable fdt = new DataTable();
                    ida.RunSql(sql, out fdt);
                    if (fdt.Rows.Count > 0)  //表示该用户是否有家庭成员 ，有的话也需要插入at_family中
                    {
                        for (int i = 0; i < fdt.Rows.Count; i++)
                        {
                            string id = System.Guid.NewGuid().ToString().Replace("-", "").ToUpper();
                            string isql = @"insert into at_family 
(ID, 姓名, 证件类型, 证件号码, 联系电话, 
住址, 关系, FID, HZID, TABLENAME, 
SID, 申请类型, IID, CID, 工龄, 住房补贴, 性别, 
单位, 住房情况, 婚否, 月收入, 备注, 职务, 通知书编号)
values ('" + id + @"', '" + fdt.Rows[i]["姓名"] + @"', '身份证', '" + fdt.Rows[i]["身份证号"] + @"', 
'" + fdt.Rows[i]["联系电话"] + @"', '', '" + fdt.Rows[i]["称谓"] + @"', '', '" + dt.Rows[0]["ID"] + "'," + @" 
'" + dt.Rows[0]["TABLENAME"] + @"', '" + dt.Rows[0]["SID"] + @"', 
'" + dt.Rows[0]["申请类型"] + @"', '" + dt.Rows[0]["IID"] + @"', '', '', '', 
'" + fdt.Rows[i]["性别"] + @"', '" + fdt.Rows[i]["工作单位"] + @"', '', '" + fdt.Rows[i]["婚姻状况"] + @"', '', 
'', '', '')";
                            ida.RunSql(isql);
                        }
                    }
                }


                ida.Close(true);
            }
            catch (Exception)
            {
                ida.Close(false);
                throw;
            }

        }

        /// <summary>
        /// 廉租房申请审批（租赁补贴）
        /// </summary>
        /// <param name="iid"></param>
        /// <param name="ownerid"></param>
        public void InsertLZFZLBT(string iid, string ownerid)
        {
            string sql = string.Empty;
            sql = @"insert into UT_洛阳低收入廉租房申请审批表
											  (IID)
											 select 
												  '" + iid + @"' IID
												from UT_洛阳低收入廉租房申请审批表";
            dataAccess().RunSql(sql);
        }

        /// <summary>
        /// 廉租房申请审批（实物配租）
        /// </summary>
        /// <param name="iid"></param>
        /// <param name="ownerid"></param>
        public void InsertLZFSWPZ(string iid, string ownerid)
        {
            string sql = string.Empty;
            sql = @"insert into UT_洛阳廉租房配租申请审批表
											  (IID)
											 select 
												  '" + iid + @"' IID
												from UT_洛阳廉租房配租申请审批表";
            dataAccess().RunSql(sql);
        }
        #endregion

        #region 征收中案件受理所用到的方法

        /// <summary>
        /// 受理时带入附件
        /// </summary>
        /// <param name="userid">操作人</param>
        /// <param name="iid"></param>
        /// <param name="ownerid">附件中的itemID</param>
        public void InsterZSFile(string userid, string iid, string ownerid, string wname, int use_type)
        {
            IDataAccess ida = dataAccess(true);
            try
            {
                string fsql = "select id,name,suffix,filecontent,itemname from zs_file where use_type=" + use_type + " and itemid='" + ownerid + "'";
                DataTable dt = new DataTable();
                dataAccess().RunSql(fsql, out dt);
                if (dt.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        string aid = Guid.NewGuid().ToString().Replace("-", "");

                        string ext_name = dt.Rows[i]["suffix"].ToString();
                        string id = dt.Rows[i]["id"].ToString();
                        string path = GetPath(dt.Rows[i]["itemname"].ToString(), wname);
                        string name = GetName(path, dt.Rows[i]["name"].ToString(), iid);
                        string asql = @"insert into st_attachment 
select '" + aid + "',1,'" + ext_name + "',filecontent,'" + name + "',null from zs_file where id = '" + dt.Rows[i]["id"] + "'";
                        string dsql = @"insert into st_dynamic_resource(IID,PATH,
                SNAME,	
                USERID,	
                TYPE,	
                RES_VALUE,	
                INPUT_INDEX,	
                VERSION,	
                ADDTIME,	
                RTID,	
                ORDERNO) 
                values('" + iid + "','" + path + name + "','受理','" + userid + "',2,'" + aid + "',null,1,null,null,0)";
                        ida.RunSql(asql);
                        ida.RunSql(dsql);

                    }
                }
                ida.Close(true);
            }
            catch (Exception)
            {
                ida.Close(false);
                throw;
            }
        }

        public string GetPath(string type, string wname)
        {

            string path = "";
            if (!string.IsNullOrEmpty(wname))
            {
                if (wname == "房屋征收冻结申请")
                {
                    if (!string.IsNullOrEmpty(type))
                    {
                        if (type == "项目批准文件")
                        {
                            path = "文档附件/建设项目批准文件/";
                        }
                        if (type == "土地权属证明文件")
                        {
                            path = "文档附件/国有土地使用权批准文件/";
                        } if (type == "规划许可文件")
                        {
                            path = "文档附件/建设用地规划许可证/";
                        } if (type == "其它")
                        {
                            path = "文档附件/其他/";
                        }
                    }
                }
                else if (wname == "房屋征收补偿决定")
                {
                    path = "送达回证附件/";
                }
                else
                {
                    path = "扫描资料/受理资料/";
                }
            }

            return path;
        }

        public string GetName(string path, string name, string iid)
        {
            string names = "";
            if (name.IndexOf('(') > -1)
            {
                name = name.Replace('(', '（');
            } if (name.IndexOf(')') > -1)
            {
                name = name.Replace(')', '）');
            }
            if (name.IndexOf('[') > -1)
            {
                name = name.Replace('[', '【');
            }
            if (name.IndexOf(']') > -1)
            {
                name = name.Replace(']', '】');
            }
            if (name.IndexOf('.') > -1)
            {
                name = name.Replace('.', '。');
            }
            if (name.IndexOf('{') > -1)
            {
                name = name.Replace('[', '【');
            }
            if (name.IndexOf('}') > -1)
            {
                name = name.Replace(']', '】');
            }
            if (name.IndexOf(' ') > -1)
            {
                name = name.Trim().Replace(' ', '-');
            }
            string count = dataAccess().GetValue("select count(*) from st_dynamic_resource where iid='" + iid + "' and path='" + path + name + "'");
            if (count == "0")
            {
                names = name;
            }
            else
            {
                count = dataAccess().GetValue("select count(*) from st_dynamic_resource where iid='" + iid + "' and path like '" + path + name + "-%'");
                if (count == "0")
                {
                    names = name + "-1";
                }
                else
                {
                    names = name + "-" + (Convert.ToInt32(count) + 1).ToString();
                }
            }
            return names;
        }
        #region 立项
        public void LiXiang(string iid, string ownerid)
        {
            string sql = @"insert into UT_房屋征收立项审批表(IID,input_index,申请单位,项目名称,项目地点,征收部门,征收实施单位,项目批准文号,规划许可证编号,用地批准文号)
select '" + iid + @"' IID,'0', 征收实施单位,项目名称,项目地址,征收部门,征收实施单位,项目批文号,用地规划许可证号,国有土地使用权批准文件 from ZS_PROJECT
where id='" + ownerid + "'";
            dataAccess().RunSql(sql);
        }
        #endregion

        #region 冻结
        public void DongJie(string iid, string ownerid)
        {
            IDataAccess ida = dataAccess(true);
            try
            {
                //房屋征收冻结通知书 
                string sql = @"insert into UT_房屋征收立项审批表(IID,input_index,项目名称,征收部门)select '" + iid + "' IID,'0',项目名称,征收部门 from ZS_PROJECT where id='" + ownerid + "'";
                //房屋征收冻结范围申请
                string ssql = @"insert into UT_房屋征收准备申请(IID,input_index,申请单位,联系电话,申请冻结期限,申请冻结期限2,征收冻结范围,权证户数,户口户数,合计,住宅,非住宅,被征收工企单位)
select '" + iid + @"'IID,'0',申请单位,电话,冻结期限开始,冻结期限结束,冻结征收范围,被征收居民权证户数,被征收居民户口户数,被征收房屋面积合计,被征收房屋面积住宅,被征收房屋面积非住宅,被征收工企单位
from ZS_PROJECT where id='" + ownerid + "'";
                ida.RunSql(sql);
                ida.RunSql(ssql);
                ida.Close(true);
            }
            catch (Exception)
            {
                ida.Close(false);
                throw;
            }

        }
        #endregion

        #region 实施
        public void ShiShi(string iid, string ownerid)
        {
            IDataAccess ida = dataAccess(true);
            try
            {
                //--征收补偿实施方案审批表              

                string sql2 = @"insert into ut_房屋征收补偿实施方案审批表(IID,input_index,申请单位,项目名称,项目地点,征收部门,征收实施单位,征收范围,合计,住宅,非住宅,
权证,工企单位,征收补偿资金概算,开户银行,到帐金额,产权置换房地点,产权置换房套数,保障房源地点,保障房源套数)
select '" + iid + @"' IID,'0',
(select 申请单位 from zs_project where id='" + ownerid + @"')申请单位,
(select 项目名称 from zs_project where id='" + ownerid + @"')项目名称,
(select 项目地址 from zs_project where id='" + ownerid + @"')项目地点,
(select 征收部门 from zs_project where id='" + ownerid + @"')征收部门,
(select 征收实施单位 from zs_project where id='" + ownerid + @"')征收实施单位,
征收范围,
房屋建筑面积,
房屋建筑面积住宅,
房屋建筑面积非住宅,
权证户数,
工企单位,
征收补偿资金,
(select 开户银行 from ZS_BCBASICINFO where pnid='" + ownerid + @"')开户银行,
(select 到账金额 from zs_bcbasicinfo where pnid='" + ownerid + @"')到账金额,
产权调换房源地点,
产权调换房源数量,
保障房源地点,
保障房源数量
from ZS_BCFAQS where pid='" + ownerid + @"' and  补偿实施=1";


                //--征收补偿实施方案审核意见书
                string ssql = @"insert into UT_房屋征收立项审批表(IID,input_index,申请单位,项目名称)
                    select '" + iid + "' IID,'0',申请单位,项目名称 from zs_project where id='" + ownerid + "'";


                ida.RunSql(sql2);
                if (ida.RunSql(sql2) < 1)
                {
                    string sql = @"insert into ut_房屋征收补偿实施方案审批表(IID,input_index,申请单位,项目名称,项目地点,征收部门,征收实施单位)
select '" + iid + @"' IID,'0',
申请单位,
项目名称,
项目地点,
征收部门,
征收实施单位
from zs_project where id='" + ownerid + "'";
                    ida.RunSql(sql);
                }
                ida.RunSql(ssql);
                ida.Close(true);
            }
            catch (Exception)
            {
                ida.Close(false);
                throw;
            }
        }
        #endregion

        #region 房屋征收补偿方案申请
        public void ZSBCFangAn(string iid, string ownerid)
        {
            IDataAccess ida = dataAccess(true);
            try
            {
                //--UT_房屋征收补偿方案论证意见表
                string sql = @"insert into UT_房屋征收补偿方案论证意见表(IID,input_index,申请单位,项目名称,项目地点,征收部门,征收实施单位,征收范围,合计,住宅,非住宅,
权证,工企单位,征收补偿资金概算,产权置换房地点,产权置换房套数,保障房源地点,保障房源套数,区属)
select '" + iid + @"' IID,'0',
(select 申请单位 from zs_project where id='" + ownerid + @"')申请单位,
(select 项目名称 from zs_project where id='" + ownerid + @"')项目名称,
(select 项目地址 from zs_project where id='" + ownerid + @"')项目地点,
(select 征收部门 from zs_project where id='" + ownerid + @"')征收部门,
(select 征收实施单位 from zs_project where id='" + ownerid + @"')征收实施单位,
征收范围,
房屋建筑面积,
房屋建筑面积住宅,
房屋建筑面积非住宅,
权证户数,
工企单位,
征收补偿资金,
产权调换房源地点,
产权调换房源数量,
保障房源地点,
保障房源数量,
(select 区属 from zs_project where id='" + ownerid + @"') 区属
from ZS_BCFAQS where pid='" + ownerid + @"' and 补偿实施=0";


                //--征收补偿实施方案审核意见书
                string ssql = @"insert into UT_房屋征收立项审批表(IID,input_index,申请单位,项目名称)
                    select '" + iid + "' IID,'0',申请单位,项目名称 from zs_project where id='" + ownerid + "'";

                ida.RunSql(sql);
                ida.RunSql(ssql);
                ida.Close(true);
            }
            catch (Exception)
            {
                ida.Close(false);
                throw;
            }
        }
        #endregion

        #region 资金
        public void ZiJin(string iid, string ownerid)
        {
            string pid = dataAccess().GetValue("select pnid from ZS_BCBASICINFO where id='" + ownerid + "'");
            string sql = @"insert into ut_征收项目补偿资金监管联系单(IID,input_index,申请单位,项目地点,开户单位,项目联系人,总建筑面积,项目编号,总补偿款,备注)
select '" + iid + @"' IID,'0',
(select 申请单位 from zs_project where id='" + pid + @"')申请单位,
(select 项目地址 from zs_project where id='" + pid + @"')项目地点,
开户银行,征收实施单位,
(select 被征收房屋面积合计 from zs_project where id='" + pid + @"')被征收房屋面积合计,
(select 立项号 from zs_project where id='" + pid + @"')项目编号,
评估总补偿款,备注
from ZS_BCBASICINFO where id='" + ownerid + "'";
            //            string sql = @"insert into ut_征收项目补偿资金监管联系单(IID,input_index,申请单位,项目地点,总建筑面积,项目编号)
            //            select '" + iid + @"' IID,'0',申请单位,项目地址,被征收房屋面积合计,立项号 from zs_project where id='"+pid+"' ";
            dataAccess().RunSql(sql);
        }
        #endregion

        #region 征收决定
        public void ZSJueDing(string iid, string ownerid)
        {
            string pid = dataAccess().GetValue("select pid from zs_prono where id='" + ownerid + "'");
            string sql = @"insert into ut_房屋征收决定审批表(IID,input_index,申请单位,项目名称,项目地点,征收部门,征收实施单位,征收范围,合计,住宅,非住宅,权证,人数,工企单位数,土地面积,概算资金)
select '" + iid + @"' IID,'0',
(select 申请单位 from zs_project where id='" + pid + @"')申请单位,
(select 项目名称 from zs_project where id='" + pid + @"')项目名称,
(select 项目地址 from zs_project where id='" + pid + @"')项目地址,
(select 征收部门 from zs_project where id='" + pid + @"')征收部门,
(select 征收实施单位 from zs_project where id='" + pid + @"')征收实施单位,
拆迁范围,
(select 被征收房屋面积合计 from zs_project where id='" + pid + @"')被征收房屋面积合计,
(select 被征收房屋面积住宅 from zs_project where id='" + pid + @"')被征收房屋面积住宅,
(select 被征收房屋面积非住宅 from zs_project where id='" + pid + @"')被征收房屋面积非住宅,
拆迁居民权证户数,
(select 户口户数 from ZS_BCFAQS where  补偿实施=1 and pid='" + pid + @"')拆迁居民人数,
拆迁工企单位个数,
(select 土地使用面积 from ZS_BCFAQS where  补偿实施=1 and pid='" + pid + @"')占地面积,
(select 概算补偿总金额 from ZS_PGGS where pid='" + pid + @"')概算总额
from ZS_PRONO where id='" + ownerid + "'";
            dataAccess().RunSql(sql);
        }
        #endregion

        #region 征收补偿决定
        public void ZSBuChang(string iid, string ownerid)
        {
            string sql = "";
            string pid = "";
            string pnid = "";
            //sql = @"select pid from zs_zzbcjfenhu where zspid='"+ownerid+"'";
            pnid = dataAccess().GetValue("select pid from zs_zzbcjfenhu where zspid='" + ownerid + "'");
            pid = dataAccess().GetValue("select pid from zs_prono where id='" + pnid + "'");
            sql = @"insert into UT_房屋征收补偿决定审批表
 (IID,input_index,征收决定号,项目名称,项目地点,征收部门,征收实施单位,产权人,公房承租人,被征收房屋坐落,被征收房屋建筑面积,被征收房屋用途,被征收房屋评估总价) 
select '" + iid + @"' IID,'0',
(select 征收许可证号 from zs_prono where id='" + pnid + @"')征收决定号,
(select 项目名称 from zs_project where id='" + pid + @"')项目名称,
(select 项目地址 from zs_project where id='" + pid + @"')项目地点,
(select 征收部门 from zs_project where id='" + pid + @"')征收部门,
(select 征收实施单位 from zs_project where id='" + pid + @"')征收实施单位,
产权人,
承租人,
房屋坐落,
房屋建筑面积,
房屋用途,
(select (to_number(t.zs室内装饰装修评估补偿金额合计)+to_number(t.fyy评估总价)+to_number(t.zz评估总价)+to_number(t.yy评估总价)) from zs_zzbcjfenhu t where t.zspid='" + ownerid + @"')被征收房屋评估总价
from zs_zsperson
where id='" + ownerid + "'";
            dataAccess().RunSql(sql);
        }
        #endregion

        #endregion

        /// <summary>
        /// 新获取用户有权限的流程
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public DataTable NewGetWorkFlowListByUserId(string userid, StepType steptype, string wnames)
        {
            string sql = "select m.*  from st_workflow m, st_step n where 1 = 1 and m.WID in (select WID from st_group_in_step where GID in (select GID from st_user_group t where t.userid = '" + userid + "') And step_type = '" + (int)steptype + "') and m.WID = n.Wid and n.step_type = '" + (int)steptype + "' and m.rot = 0  and m.wname in(" + wnames + ")";
            DataTable dt;
            dataAccess().RunSql(sql, out dt);
            return dt;
        }

        /// <summary>
        /// 新获取用户所能受理的流程信息
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public DataTable NewGetStartWorkFlowListByUserId(string userid, string wname)
        {
            return NewGetWorkFlowListByUserId(userid, StepType.Start, wname);
        }

        /// <summary>
        /// 获取用户有权限的所有流程
        /// </summary>
        /// <returns></returns>
        public DataTable NewGetUserRightWname(string userid, string wnames)
        {
            string sql = "";
            string query = "";
            if (!string.IsNullOrEmpty(userid))
            {
                query = " and ws.userid='" + userid + "' ";
            }
            string OfficeSearch = System.Configuration.ConfigurationManager.AppSettings["OfficeSearch"].ToString();
            if (OfficeSearch.IndexOf(userid) == 0)
            {
                sql = @"Select * From St_Workflow Where  rot=0 And wname In(" + System.Configuration.ConfigurationManager.AppSettings["ZSAnJianType"].ToString() + ")";
            }
            else
            {
                sql = " SELECT * FROM (select distinct wname from st_workflow w,st_instance i,st_work_item_stack ws where w.wid=i.wid and i.iid=ws.iid " + query + "  and w.wname in(" + wnames + ")) order by wname";
            }

            DataTable dt = new DataTable();
            dataAccess().RunSql(sql, out dt);
            return dt;
        }

        /// <summary>
        /// 获取有权限的查询列表数据信息
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public DataTable NewGetUserRightWorkFlow(string query, string userid)
        {
            string adminid = System.Configuration.ConfigurationManager.AppSettings["OfficeSearch"].ToString();
            string[] aids = adminid.Split(',');
            string sql = "";
            bool flag = false;
            foreach (string item in aids)
            {
                if (userid == item)
                {
                    flag = true;
                }
            }
            if (flag)
            {
                sql = @"select i.*,w.wname,(Select caseid From zs_case_register Where iid=i.iid) caseid  from st_instance i, st_workflow w  where w.wid = i.wid " + query + " order by i.iid desc";
            }
            else
            {
                sql = @"select i.*,w.wname,(Select caseid From zs_case_register Where iid=i.iid) caseid  from st_instance i, st_workflow w,St_Work_Item_Hist wi
  where w.wid = i.wid and i.iid=wi.iid " + query + @"
  and wi.userid='" + userid + @"' or wi.userid in (select userid from st_delegate Where delegate_userid = '" + userid + @"' 
And (delegate_task = w.wname or delegate_task = '全部') and begin_time <= Sysdate 
And end_time >= Sysdate) order by i.iid desc";
            }
            //  string sql = @"select i.*,w.wname,(Select caseid From zs_case_register Where iid=i.iid) caseid  from st_instance i, st_workflow w  where w.wid = i.wid " + query + " order by i.iid desc";                      
            DataTable dt = new DataTable();
            dataAccess().RunSql(sql, out dt);
            return dt;
        }

        public string GetFidByFName(string fname)
        {
            return dataAccess().GetValue(@"select res_value from st_static_resource where path ='" + fname + "'");
        }

        public bool TaskOpenCheck(string iid, string wiid, string step, string userId, out string msg)
        {
            DataTable table;
            msg = "";
            string sql = "SELECT wiid,locked,Active FROM ST_Work_Item WHERE  iid='" + iid + "' AND wiid=" + wiid;
            dataAccess().RunSql(sql, out table);
            if (table.Rows.Count == 0)
            {
                msg = "此业务不属于本岗位处理，不能打开，可能是已提交到下一岗位了！";
                return false;
            }
            if ((table.Rows[0]["Active"].ToString() != "1") && (table.Rows[0]["locked"].ToString().Trim() != "2"))
            {
                sql = @"SELECT USERID FROM ST_Work_Item WHERE Active=1 AND StepCtlId='" + step + "' AND iid='" + iid + "' AND UserId<>'" + userId + "'";
                string str2 = string.Empty;
                DataTable table2;
                dataAccess().RunSql(sql, out table2);
                if (table2.Rows.Count > 0)
                {
                    str2 = table2.Rows[0][0].ToString();
                }
                if (!string.IsNullOrEmpty(str2))
                {
                    sql = "SELECT USER_NAME FROM ST_USER WHERE USERID='" + str2 + "'";
                    string str3 = dataAccess().GetValue(sql);
                    msg = "此业务已被同岗位的" + str3 + "打开接收了，不能打开！";
                    return false;
                }
                sql = string.Format(@"UPDATE ST_WORK_ITEM SET active=1,userid='{0}' WHERE iid='{1}' AND STEPCtlId='{2}' 
AND ( userid='{0}'  OR UserId in(select userid from st_delegate  
Where delegate_userid='{0}' And (delegate_task IN(SELECT a.WNAME FROM St_WORKFLOW a,
st_Instance b where a.wid=b.wid and b.iid=ST_WORK_ITEM.iid ) or delegate_task='全部') 
and begin_time<=Sysdate And end_time>=Sysdate)) ", userId, iid, step);
                dataAccess().RunSql(sql);
                sql = string.Format(@"UPDATE ST_WORK_ITEM SET active=-1 WHERE iid='{0}' AND StepCtlId='{1}'
AND wiid<>'{2}' AND locked<>2",iid,step,wiid);
                dataAccess().RunSql(sql);
            }
            try
            {
                IDataParameter[] prams = DataFactory.GetParameter(DatabaseType.Oracle, 4);
                prams[0].ParameterName = "iUserId";
                prams[0].Value = userId;
                prams[1].ParameterName = "iSerial";
                prams[1].Value = long.Parse(iid);
                prams[2].ParameterName = "workItem";
                prams[2].Value = long.Parse(wiid);
                prams[3].ParameterName = "iStepName";
                prams[3].Value = step;
                dataAccess().RunProc("PublicEvents.WhenTaskOpen", ref prams);
            }
            catch (Exception exception)
            {
                Antu.Utilities.UtilitiesPage.AlertDialogClear("alert('打开业务时发生错误,User=" + userId + ",IID=" + iid + ",Step=" + step + ",错误信息为" + exception.Message + "\n,出错堆栈为" + exception.StackTrace + "！');", "window.parent.closeDialog();");
            }
            return true;
        }
        public string GetWidByIID(string iid)
        {
            string sql = "SELECT Wid FROM St_Instance WHERE iid='" + iid + "'";
            return dataAccess().GetValue(sql);
        }
        public DataTable ListInstanceResource(string InstanceID)
        {
            DataTable table;
            DataTable table2;
            string sql = string.Format("SELECT WID FROM ST_INSTANCE WHERE IID='{0}'", InstanceID);
            string str3 = string.Empty;
            IDataAccess access = dataAccess();
            str3 = access.GetValue(sql);
            sql = string.Format("SELECT TYPE,RES_VALUE,PATH,0 input_index,rtid FROM st_static_resource WHERE WID='{0}' AND SHOW=1 Order By orderno", str3);
            access.RunSql(sql, out table);
            sql = string.Format("SELECT TYPE,RES_VALUE,PATH,case when input_index is null then 0 else input_index end input_index,rtid  FROM ST_DYNAMIC_RESOURCE WHERE IID='{0}' ORDER BY OrderNo", InstanceID);
            access.RunSql(sql, out table2);
            for (int i = 0; i < table2.Rows.Count; i++)
            {
                DataRow row = table.NewRow();
                row["Type"] = table2.Rows[i]["Type"].ToString();
                string str4 = table2.Rows[i]["Res_Value"].ToString();
                if (table2.Rows[i]["Type"].ToString().Trim() == "1")
                {
                    string nameValue = string.Empty;
                    string oldValue = string.Empty;
                    if (str4.IndexOf("【IP:") >= 0)
                    {
                        nameValue = str4;
                        nameValue = nameValue.Substring(nameValue.IndexOf("【IP:"), (nameValue.IndexOf("】") - nameValue.IndexOf("【IP:")) + 1);
                        oldValue = nameValue;
                        nameValue = nameValue.Replace("【IP:", "").Replace("】", "").Trim();
                        nameValue = GetServerNameOrValue(nameValue);
                        str4 = str4.Replace(oldValue, nameValue);
                    }
                }
                row["Res_Value"] = str4;
                row["Path"] = table2.Rows[i]["Path"].ToString();
                row["input_index"] = table2.Rows[i]["input_index"];
                row["rtid"] = table2.Rows[i]["rtid"];
                table.Rows.Add(row);
            }
            table.AcceptChanges();
            return table;
        }
        private string GetServerNameOrValue(string NameValue)
        {
            string sql = string.Format("SELECT SERVER FROM ST_SERVER S WHERE S.TYPE=1 AND S.IDENTIFY='{0}'", NameValue);
            return dataAccess().GetValue(sql);
        }
        public DataTable ListInstanceStepResource(string wiid, string ctlId, string iid)
        {
            DataTable table;
            DataTable table2;
            DataTable table3;
            string sql = string.Format("SELECT WId FROM ST_INSTANCE WHERE IID='{0}'", iid);
            IDataAccess access = dataAccess();
            string str3 = access.GetValue(sql);
            sql = @"SELECT Rtid,TYPE,RES_VALUE,PATH,0 input_index FROM st_static_resource WHERE WID='" + str3 + @"' And  SHOW=1  
AND rtid IN (SELECT rtid FROM st_resource_right WHERE WID='" + str3 + @"' AND sctlid='" + ctlId + @"' ) Order By orderno";
            access.RunSql(sql, out table);
            sql = "SELECT rtid,TYPE,RES_VALUE,PATH,case when input_index is null then 0 else input_index end input_index FROM ST_DYNAMIC_RESOURCE WHERE IID='{0}'  ORDER BY OrderNo";
            sql = string.Format(sql, iid, str3, ctlId);
            access.RunSql(sql, out table2);
            sql = "SELECT S.IDENTIFY,SERVER FROM ST_SERVER S WHERE S.TYPE=1";
            access.RunSql(sql, out table3);
            DataView defaultView = table3.DefaultView;
            for (int i = 0; i < table2.Rows.Count; i++)
            {
                DataRow row = table.NewRow();
                row["rtid"] = table2.Rows[i]["rtid"].ToString();
                row["Type"] = table2.Rows[i]["Type"].ToString();
                string str4 = table2.Rows[i]["Res_Value"].ToString();
                if (table2.Rows[i]["Type"].ToString().Trim() == "1")
                {
                    string nameValue = string.Empty;
                    string oldValue = string.Empty;
                    if (str4.IndexOf("【IP:") >= 0)
                    {
                        nameValue = str4;
                        nameValue = nameValue.Substring(nameValue.IndexOf("【IP:"), (nameValue.IndexOf("】") - nameValue.IndexOf("【IP:")) + 1);
                        oldValue = nameValue;
                        nameValue = nameValue.Replace("【IP:", "").Replace("】", "").Trim();
                        nameValue = GetServerNameOrValue(nameValue);
                        str4 = str4.Replace(oldValue, nameValue);
                    }
                }
                row["Res_Value"] = str4;
                row["Path"] = table2.Rows[i]["Path"].ToString();
                row["input_index"] = table2.Rows[i]["input_index"];
                table.Rows.Add(row);
            }
            table.AcceptChanges();
            return table;
        }
        public StepType GetStepTypeByStep(string wid, string stepCtlId)
        {
            string sql = "SELECT Step_Type From ST_Step WHERE wid='" + wid + "' AND CtlId='" + stepCtlId + "'";
            DataTable table;
            dataAccess().RunSql(sql, out table);
            string str = string.Empty;
            if (table.Rows.Count > 0)
            {
                str = table.Rows[0][0].ToString();
            }
            return (StepType)S2Int(str);
        }
        public StepType GetStepTypeByStep(long iid, string stepCtlId)
        {
            return GetStepTypeByStep(GetWidByIID(iid.ToString()), stepCtlId);
        }
        
        public bool NewInstance(string wid, string userId, string ownerName, ref string iid, ref string wiid, ref string errMsg)
        {
            bool flag;
            IDataAccess ida = dataAccess(true);
            try
            {
                if (NewInstance(wid, userId, ownerName, ref iid, ref wiid, ref errMsg, ref ida))
                {
                    ida.Close(true);
                    return true;
                }
                ida.Close(false);
                flag = false;
            }
            catch (Exception exception)
            {
                ida.Close(false);
                throw exception;
            }
            return flag;
        }
        public bool NewInstance(string wid, string userId, string ownerName, ref string iid, ref string wiid, ref string errMsg, ref IDataAccess ida)
        {
            return NewInstance(wid, userId, ownerName, ref iid, ref wiid, string.Empty, string.Empty, ref errMsg, ref ida);
        }
        public bool NewInstance(string wid, string userId, string ownerName, ref string iid, ref string wiid, string ext1, string ext2, ref string errMsg, ref IDataAccess idda)
        {
            DataTable table;
            string sql = "";
            errMsg = "";
            Step step = null;
            WorkflowType shenPi = WorkflowType.ShenPi;
            int num = 0;
            int num2 = 0;
            sql = "SELECT * FROM ST_WORKFLOW WHERE WID='" + wid + "'";
            dataAccess().RunSql(sql, out table);
            if (table.Rows.Count > 0)
            {
                DataRow row = table.Rows[0];
                shenPi = (WorkflowType)S2Int(row["type"].ToString());
                num = S2Int(row["TimeOut"].ToString());
                num2 = S2Int(row["ExAlarmTime"].ToString());
            }
            sql = "SELECT stpName FROM ST_Group_In_Step WHERE Step_Type=0 AND Wid='" + wid + "' AND Gid IN(SELECT GID FROM ST_USER_GROUP WHERE USERID='" + userId + "')";
            string str2 = dataAccess().GetValue(sql);
            if (string.IsNullOrEmpty(str2))
            {
                if (((shenPi != WorkflowType.FaWen) && (shenPi != WorkflowType.ShouWen)) && (shenPi != WorkflowType.YueWen))
                {
                    errMsg = "你没有权限为此流程新建业务，请授予权限后再执行此操作！";
                    return false;
                }
                sql = "SELECT stpName FROM ST_Group_In_Step WHERE Step_Type=0 AND Wid='" + wid + "' ";
                str2 = dataAccess().GetValue(sql);
                step = new Step(wid, str2);
            }
            else
            {
                step = new Step(wid, str2);
            }
            IDataAccess ida = dataAccess();
            if (string.IsNullOrEmpty(ext1) && string.IsNullOrEmpty(ext2))
            {
                iid = GetIID(wid, userId, ownerName, ref ida).ToString();
            }
            else
            {
                iid = GetIID(wid, userId, ownerName, ext1, ext2, ref ida).ToString();
            }
            string str3 = "null";
            sql = "SELECT SUM(timeOut) FROM ST_STEP WHERE wid='" + wid + "'";
            string str4 = dataAccess().GetValue(sql);
            if ((num > 0) && ((step.Timeout > 0) || (string.IsNullOrEmpty(str4) || (str4.Trim() == "0"))))
            {
                str3 = "sysdate";
            }
            sql = @"insert into St_instance(iid,name, wid, status, priority, accepted_time,exbeginmonitor,extotaltime,exalarmtime) 
values('" + iid + "','" + ownerName + "','" + wid + "',1,0,Sysdate," + str3 + ",'" + num + "','" + num2 + "')";
            idda.RunSql(sql);

            bool isRound = false;
            if (shenPi == WorkflowType.YueWen)
            {
                isRound = true;
            }
            NewItem(wid, iid, "0", userId, step.Sname, str2, string.Empty, string.Empty, string.Empty, string.Empty, false, ref wiid, ref idda, isRound, string.Empty, string.Empty);
            return true;
        }
        public bool NewItem(string wid, string iid, string prev_wiid, string userId, string step, string stepCtlId, string sendStepCtlId, string sendStep, string sendUserId, string remark, bool isBack, ref string wiid, ref IDataAccess dataAccess1, bool IsRound, string stepGroupCtlId, string stepGroup)
        {
            IDataAccess access = dataAccess();
            wiid = GetWorkItemMax(long.Parse(iid), ref dataAccess1).ToString();
            string sql = "SELECT * FROM ST_STEP WHERE Wid=:wid AND CtlId=:sname";
            IDataParameter[] parameter = DataFactory.GetParameter(DatabaseType.Oracle, 2);
            parameter[0].ParameterName = "wid";
            parameter[0].DbType = DbType.String;
            parameter[0].Value = wid;
            parameter[1].ParameterName = "sname";
            parameter[1].DbType = DbType.String;
            parameter[1].Value = stepCtlId;
            if (!string.IsNullOrEmpty(stepGroupCtlId))
            {
                parameter[1].Value = stepGroupCtlId;
            }
            DataTable table = access.RunSql(sql, ref parameter);
            if (table.Rows.Count <= 0)
            {
                throw new Exception("没有在节点定义表中找到数据,无法完成操作！");
            }
            string str2 = table.Rows[0]["TimeOut"].ToString();
            string str3 = table.Rows[0]["ExAlarmTime"].ToString();
            if (string.IsNullOrEmpty(stepGroupCtlId))
            {
                step = table.Rows[0]["SName"].ToString();
            }
            string str4 = "null";
            if (!string.IsNullOrEmpty(str2) && (str2.Trim() != "0"))
            {
                DataTable table2;
                str4 = "sysdate";
                sql = "SELECT exbeginmonitor FROM ST_Instance WHERE iid=" + iid;
                dataAccess1.RunSql(sql, out table2);
                if ((table2.Rows.Count > 0) && string.IsNullOrEmpty(table2.Rows[0][0].ToString()))
                {
                    sql = "UPDATE ST_Instance SET exbeginmonitor=sysdate WHERE iid=" + iid;
                    dataAccess1.RunSql(sql);
                }
            }
            string str5 = "0";
            if (isBack)
            {
                str5 = "1";
            }
            string str6 = "0";
            if (IsRound)
            {
                str6 = "2";
            }
            string str7 = "INSERT INTO st_work_item(iid,wiid, prev_wiid, userid, step, locked, accepted_time,isback,extotaltime,exalarmtime,exreceivertype,exbeginmonitor,sendstep,senduserid,remark,stepgroup,STEPCTLID,SENDSTEPCTLID,STEPGROUPCTLID) VALUES(:iid,:wiid, :prev_wiid, :userid, :step, :locked,sysdate,:isback,:extotaltime,:exalarmtime,1," + str4 + ",:sendstep,:senduserid,:remark,:stepgroup,:stepctlid,:sendstepctlid,:stepgroupctlid)";
            parameter = DataFactory.GetParameter(DatabaseType.Oracle, 0x10);
            parameter[0].ParameterName = "iid";
            parameter[0].DbType = DbType.Decimal;
            parameter[0].Value = iid;
            parameter[1].ParameterName = "wiid";
            parameter[1].DbType = DbType.Decimal;
            parameter[1].Value = wiid;
            parameter[2].ParameterName = "prev_wiid";
            parameter[2].DbType = DbType.Decimal;
            parameter[2].Value = prev_wiid;
            parameter[3].ParameterName = "userid";
            parameter[3].DbType = DbType.String;
            parameter[3].Value = userId;
            parameter[4].ParameterName = "step";
            parameter[4].DbType = DbType.String;
            parameter[4].Value = step;
            parameter[5].ParameterName = "locked";
            parameter[5].DbType = DbType.String;
            parameter[5].Value = str6;
            parameter[6].ParameterName = "isback";
            parameter[6].DbType = DbType.Int32;
            parameter[6].Value = str5;
            parameter[7].ParameterName = "extotaltime";
            parameter[7].DbType = DbType.Decimal;
            parameter[7].Value = str2;
            parameter[8].ParameterName = "exalarmtime";
            parameter[8].DbType = DbType.Decimal;
            parameter[8].Value = str3;
            parameter[9].ParameterName = "sendstep";
            parameter[9].DbType = DbType.String;
            parameter[9].Value = sendStep;
            parameter[10].ParameterName = "senduserid";
            parameter[10].DbType = DbType.String;
            parameter[10].Value = sendUserId;
            parameter[11].ParameterName = "remark";
            parameter[11].DbType = DbType.String;
            parameter[11].Value = remark;
            parameter[12].ParameterName = "stepgroup";
            parameter[12].DbType = DbType.String;
            parameter[12].Value = stepGroup;
            parameter[13].ParameterName = "stepctlid";
            parameter[13].DbType = DbType.String;
            parameter[13].Value = stepCtlId;
            parameter[14].ParameterName = "sendstepctlid";
            parameter[14].DbType = DbType.String;
            parameter[14].Value = sendStepCtlId;
            parameter[15].ParameterName = "stepgroupctlid";
            parameter[15].DbType = DbType.String;
            parameter[15].Value = stepGroupCtlId;
            dataAccess1.RunSql(str7, ref parameter);
            if (!isBack)
            {
                sql = "INSERT INTO ST_WORK_ITEM_STACK(IID,WIID,PREV_WIID,USERID,STEP) VALUES(:iid,:wiid,:prev_wiid,:userid,:step)";
                parameter = DataFactory.GetParameter(DatabaseType.Oracle, 5);
                parameter[0].ParameterName = "iid";
                parameter[0].DbType = DbType.Decimal;
                parameter[0].Value = iid;
                parameter[1].ParameterName = "wiid";
                parameter[1].DbType = DbType.Decimal;
                parameter[1].Value = wiid;
                parameter[2].ParameterName = "prev_wiid";
                parameter[2].DbType = DbType.Decimal;
                parameter[2].Value = prev_wiid;
                parameter[3].ParameterName = "userid";
                parameter[3].DbType = DbType.String;
                parameter[3].Value = userId;
                parameter[4].ParameterName = "step";
                parameter[4].DbType = DbType.String;
                parameter[4].Value = stepCtlId;
                dataAccess1.RunSql(sql, ref parameter);
            }
            return true;
        }
        public long GetIID(string wid, string userId, string ownerName, ref IDataAccess ida)
        {
            IDataParameter[] parameter = DataFactory.GetParameter(DatabaseType.Oracle, 4);
            parameter[0].ParameterName = "wid";
            parameter[0].Value = wid;
            parameter[1].ParameterName = "userId";
            parameter[1].Value = userId;
            parameter[2].ParameterName = "ownerName";
            parameter[2].Value = ownerName;
            parameter[3].ParameterName = "returnValue";
            parameter[3].Direction = ParameterDirection.Output;
            parameter[3].DbType = DbType.Int64;
            dataAccess().RunProc("PublicEvents.GetIIdNumber", ref parameter);
            return long.Parse(parameter[3].Value.ToString());
        }
        public long GetIID(string wid, string userId, string ownerName, string ex1, string ex2, ref IDataAccess ida)
        {
            IDataParameter[] parameter = DataFactory.GetParameter(DatabaseType.Oracle, 6);
            parameter[0].ParameterName = "wid";
            parameter[0].Value = wid;
            parameter[1].ParameterName = "userId";
            parameter[1].Value = userId;
            parameter[2].ParameterName = "ownerName";
            parameter[2].Value = ownerName;
            parameter[3].ParameterName = "ex1";
            parameter[3].Value = ex1;
            parameter[4].ParameterName = "ex2";
            parameter[4].Value = ex2;
            parameter[5].ParameterName = "returnValue";
            parameter[5].Direction = ParameterDirection.Output;
            parameter[5].DbType = DbType.Int64;
            dataAccess().RunProc("PublicEvents.GetIIdNumberExt", ref parameter);
            return long.Parse(parameter[5].Value.ToString());
        }
        public int S2Int(string str)
        {
            return S2Int(str, 0);
        }
        public int S2Int(string str, int defaultValue)
        {
            if (!string.IsNullOrEmpty(str) && IsNum(str))
            {
                return (int)S2Float(str);
            }
            return defaultValue;
        }
        public bool IsNum(string num)
        {
            if (string.IsNullOrEmpty(num))
            {
                return false;
            }
            int num2 = 0;
            for (int i = 0; i < num.Length; i++)
            {
                if ("1234567890".IndexOf(num[i]) < 0)
                {
                    if (num[i] == '.')
                    {
                        if ((i <= 0) || (i >= (num.Length - 1)))
                        {
                            return false;
                        }
                        num2++;
                    }
                    else if ((i != 0) || (num[i] != '-'))
                    {
                        return false;
                    }
                }
            }
            return ((num2 == 0) || (num2 == 1));
        }
        public float S2Float(string str)
        {
            return S2Float(str, 0f);
        }
        public float S2Float(string str, float defaultValue)
        {
            if (!string.IsNullOrEmpty(str) && IsNum(str))
            {
                return float.Parse(str);
            }
            return defaultValue;
        }
        public Color StringChangeToColor(string colorValue)
        {
            ColorConverter converter = new ColorConverter();
            return (Color)converter.ConvertFromString("#" + colorValue);
        }
        public string ColorChangeToString(Color color)
        {
            return color.ToArgb().ToString("x").Remove(0, 2);
        }
        public void GetGraphicText(Font font, string text, float rectWidth, float rectHeight, float pointX, float pointY, Graphics g, ref float x, ref float y)
        {
            Point point = new Point();
            SizeF ef = g.MeasureString(text, font);
            x = pointX + ((rectWidth - ef.Width) / 2f);
            y = pointY + ((rectHeight - ef.Height) / 2f);
        }
        public long GetWorkItemMax(long iid, ref IDataAccess ida)
        {
            if (iid.ToString().Length > 8)
            {
                DataTable table;
                string sql = "SELECT Max(wiid)+1  From st_work_item Where iid='" + iid + "'";
                ida.RunSql(sql, out table);
                if (table.Rows.Count > 0)
                {
                    string str2 = table.Rows[0][0].ToString();
                    if ((string.IsNullOrEmpty(str2) || (str2 == "0")) || (str2.Length < 8))
                    {
                        str2 = iid.ToString() + "001";
                    }
                    return long.Parse(str2);
                }
                return 1L;
            }
            return Get("ss_workitem_id", ref ida);
        }
        public long Get(string seedName, ref IDataAccess ida)
        {
            IDataParameter[] parameter = DataFactory.GetParameter(DatabaseType.Oracle, 2);
            parameter[0].ParameterName = "iName";
            parameter[0].Value = seedName;
            parameter[1].ParameterName = "returnValue";
            parameter[1].Direction = ParameterDirection.Output;
            parameter[1].DbType = DbType.Int64;
            dataAccess().RunProc("PublicEvents.GetSeedNumber", ref parameter);
            return long.Parse(parameter[1].Value.ToString());
        }
        public bool CheckTableRight(string iid, string wid, string step, string rtid, string resValue, string userId)
        {
            string sql = "select Count(t.wid) from st_workflow_right t Where t.wid='" + wid + "' And t.sctlid='" + step + "' And t.rtid='" + rtid + "'  And t.input_right>1 AND (t.ROLEID IS NULL OR EXISTS(SELECT g.GID FROM st_user_group g WHERE t.Roleid=g.Gid AND g.USERID='" + userId + "') )";
            return (dataAccess().GetValue(sql) != "0");
        }
        public SendMethod GetSendMethod(long iid, string step)
        {
            return GetSendMethod(GetWidByIID(iid.ToString()), step);
        }
        public SendMethod GetSendMethod(string wid, string step)
        {
            string sql = "SELECT Send_Method FROM ST_STEP WHERE Wid=:wid AND ctlid=:sname";
            IDataParameter[] parameter = DataFactory.GetParameter(DatabaseType.Oracle, 2);
            parameter[0].ParameterName = "wid";
            parameter[0].DbType = DbType.String;
            parameter[0].Value = wid;
            parameter[1].ParameterName = "sname";
            parameter[1].DbType = DbType.String;
            parameter[1].Value = step;
            string str2 = string.Empty;
            DataTable table = dataAccess().RunSql(sql, ref parameter);
            if (table.Rows.Count > 0)
            {
                str2 = table.Rows[0][0].ToString();
            }
            if (string.IsNullOrEmpty(str2))
            {
                return SendMethod.AllSend;
            }
            return (SendMethod)S2Int(str2);
        }
        public DataTable GetStepAllNextFlowNames(long iid, long wiid, string step)
        {
            return GetStepAllNextFlowNames(iid, wiid, step, false);
        }
        public DataTable GetStepAllNextFlowNames(long iid, long wiid, string step, bool mustInStep)
        {
            string widByIID = GetWidByIID(iid.ToString());
            string sql = "SELECT InStepType FROM ST_Step WHERE wid=:wid AND ctlid=:sname";
            IDataParameter[] parameter = DataFactory.GetParameter(DatabaseType.Oracle, 2);
            parameter[0].ParameterName = "wid";
            parameter[0].DbType = DbType.String;
            parameter[0].Value = widByIID;
            parameter[1].ParameterName = "sname";
            parameter[1].DbType = DbType.String;
            parameter[1].Value = step;
            string str = string.Empty;
            DataTable table = dataAccess().RunSql(sql, ref parameter);
            if (table.Rows.Count > 0)
            {
                str = table.Rows[0][0].ToString();
            }
            InStepSumbitType type = (InStepSumbitType)S2Int(str);
            sql = "select COUNT(a.gid) from st_group_in_step a,st_user_group b,st_user c  Where a.gid=b.gid And b.userid=c.userid And  wid=:wid And stpname=:sname";
            parameter = DataFactory.GetParameter(DatabaseType.Oracle, 2);
            parameter[0].ParameterName = "wid";
            parameter[0].DbType = DbType.String;
            parameter[0].Value = widByIID;
            parameter[1].ParameterName = "sname";
            parameter[1].DbType = DbType.String;
            parameter[1].Value = step;
            table = dataAccess().RunSql(sql, ref parameter);
            string str4 = string.Empty;
            if (table.Rows.Count > 0)
            {
                str4 = table.Rows[0][0].ToString();
            }
            sql = "SELECT FName,condition_str,ename FROM ST_Flow WHERE wid=:wid And sname=:sname ";
            if ((mustInStep || (type == InStepSumbitType.MustSumbit)) && (S2Int(str4) > 1))
            {
                sql = sql + " AND 1=2";
            }
            sql = sql + " order by sort";
            parameter = DataFactory.GetParameter(DatabaseType.Oracle, 2);
            parameter[0].ParameterName = "wid";
            parameter[0].DbType = DbType.String;
            parameter[0].Value = widByIID;
            parameter[1].ParameterName = "sname";
            parameter[1].DbType = DbType.String;
            parameter[1].Value = step;
            table = dataAccess().RunSql(sql, ref parameter);
            int count = table.Rows.Count;
            for (int i = 0; i < count; i++)
            {
                if (!string.IsNullOrEmpty(table.Rows[i]["condition_str"].ToString()))
                {
                    string str6 = table.Rows[i]["condition_str"].ToString().ToUpper().Replace("{IID}", iid.ToString()).Replace("{STEP}", step);
                    if (string.IsNullOrEmpty(dataAccess().GetValue(str6)))
                    {
                        table.Rows.RemoveAt(i);
                        table.AcceptChanges();
                        i--;
                        count--;
                    }
                }
            }
            sql = "select locked from st_work_item where iid=:iid and wiid=:wiid";
            parameter = DataFactory.GetParameter(DatabaseType.Oracle, 2);
            parameter[0].ParameterName = "iid";
            parameter[0].DbType = DbType.Decimal;
            parameter[0].Value = iid;
            parameter[1].ParameterName = "wiid";
            parameter[1].DbType = DbType.Decimal;
            parameter[1].Value = wiid;
            DataTable table2 = dataAccess().RunSql(sql, ref parameter);
            string str7 = string.Empty;
            if (table2.Rows.Count > 0)
            {
                str7 = table2.Rows[0][0].ToString();
            }
            if (str7 != "2")
            {
                DataRow row = table.NewRow();
                row["FName"] = "岗位内提交";
                switch (type)
                {
                    case InStepSumbitType.NeedSumbit:
                        if (S2Int(str4) > 1)
                        {
                            table.Rows.Add(row);
                        }
                        break;

                    case InStepSumbitType.MustSumbit:
                        if (S2Int(str4) > 1)
                        {
                            table.Rows.Add(row);
                        }
                        break;
                }
                table.AcceptChanges();
            }
            return table;
        }
        public StepType GetStepType(long iid, string flow, string sname)
        {
            return GetStepType(GetWidByIID(iid.ToString()), flow, sname);
        }
        public StepType GetStepType(string wid, string flow, string sname)
        {
            string sql = "SELECT Step_Type From ST_Step WHERE wid=:wid AND ctlid IN(SELECT EName FROM ST_Flow WHERE wid=:wid AND FName=:fname)";
            IDataParameter[] parameter = DataFactory.GetParameter(DatabaseType.Oracle, 2);
            parameter[0].ParameterName = "fname";
            parameter[0].DbType = DbType.String;
            parameter[0].Value = flow;
            parameter[1].ParameterName = "wid";
            parameter[1].DbType = DbType.String;
            parameter[1].Value = wid;
            DataTable table = dataAccess().RunSql(sql, ref parameter);
            string str = string.Empty;
            if (table.Rows.Count > 0)
            {
                str = table.Rows[0][0].ToString();
            }
            return (StepType)S2Int(str);
        }
        public bool Stop(long iid, long wiid, string remark)
        {
            IDataAccess ida = dataAccess(true);
            try
            {
                if (!this.Stop(iid, wiid, remark, ref ida))
                {
                    ida.Close(false);
                    return false;
                }
                ida.Close(true);
                return true;
            }
            catch
            {
                ida.Close(false);
                return false;
            }
        }
        public bool Stop(long iid, long wiid, string remark, ref IDataAccess ida)
        {
            return this.Stop(iid, wiid, remark, -2, ref ida);
        }
        public bool Stop(long iid, long wiid, string remark, int state, ref IDataAccess ida)
        {
            string sql = "UPDATE ST_WORK_ITEM SET IsBack=2 WHERE Wiid=" + wiid.ToString();
            ida.RunSql(sql);
            if (!this.ToHist(wiid.ToString(), ref ida))
            {
                return false;
            }
            sql = "DELETE FROM ST_WORK_ITEM WHERE iid='" + iid.ToString() + "' AND Active=-1";
            ida.RunSql(sql);
            sql = string.Concat(new object[] { "UPDATE ST_INSTANCE SET STATUS=", state, ",finish_time=sysdate,remark='", remark, "' WHERE iid='", iid.ToString(), "'" });
            ida.RunSql(sql);
            return true;
        }
        public void UpdatePlFileContent(string tablename, string colname, byte[] FileContent, string filename, string filext, string itemid,string itemname,string usetype)
        {
            string strAId = System.Guid.NewGuid().ToString().Replace("-", "").ToUpper();
            string sql = "insert into zs_file (id,name,suffix,itemid,itemname,use_type) values('" + strAId + "','" + filename + "','" + filext + "','" + itemid + "','" + itemname + "','" + usetype + "')";
            dataAccess().RunSql(sql);
            try
            {
                System.Data.OracleClient.OracleConnection oc = new System.Data.OracleClient.OracleConnection();
                oc.ConnectionString = System.Configuration.ConfigurationManager.AppSettings["OracleConnectionString"].ToString();
                System.Data.OracleClient.OracleCommand cmd = new System.Data.OracleClient.OracleCommand("update zs_file set FILECONTENT=:bFileContent where id='" + strAId + "'", oc);
                System.Data.OracleClient.OracleParameter op1 = new System.Data.OracleClient.OracleParameter("bFileContent", System.Data.OracleClient.OracleType.Blob, FileContent.Length);
                op1.Value = FileContent;
                cmd.Parameters.Add(op1);
                oc.Open();
                cmd.ExecuteNonQuery();
                oc.Close();
            }
            catch
            {

            }
        }

    }
}
